John Davidson

Message:

Why DISTINCT UID doesn't work in my code below?

$q = mysql_fetch_array(mysql_query("SELECT COUNT(DISTINCT UID) AS TOTAL, 

     SUM(CASE WHEN SYSTEM = 'Android' THEN 1 ELSE 0 END) AS A,

     SUM(CASE WHEN SYSTEM = 'IOS' THEN 1 ELSE 0 END) AS I,

     SUM(CASE WHEN SYSTEM = 'Windows' THEN 1 ELSE 0 END) AS W FROM user_visits"));

The query returns all items from the database selected even I put DISTINCT UID or *. Same result.

---

My Answer:
The issue with your query is that you are using the COUNT function with DISTINCT on the UID column, but you are not actually selecting the UID column in your query.

To fix this issue, you need to include the UID column in your SELECT statement like this:

php
$q = mysql_fetch_array(mysql_query("SELECT COUNT(DISTINCT UID) AS TOTAL, UID,
SUM(CASE WHEN SYSTEM = 'Android' THEN 1 ELSE 0 END) AS A,
SUM(CASE WHEN SYSTEM = 'IOS' THEN 1 ELSE 0 END) AS I,
SUM(CASE WHEN SYSTEM = 'Windows' THEN 1 ELSE 0 END) AS W FROM user_visits"));


By including the UID column in your SELECT statement, the COUNT(DISTINCT UID) function will work as expected and give you the distinct count of UID values in the user_visits table.