CODEWITHSUNDEEP
mysql
user783322
user783322

Reputation: 481

MySQL if statement returning incorrect results with aggregate function

I have a query 

SELECT
    itemId, userId, source
FROM table
WHERE itemId = 1 AND userId = 1 and source = 1;

It returns 20 rows where the itemId is 1, the userId is 1 and the source is 1 (for-instance). In my db there are more rows for this itemId and this userId, which have with different sources. I want to get some stats on this.

Why then does this query return 0 for both the unique and total rows?

SELECT ID, 
    IF(source = 1, COUNT(DISTINCT userId), 0) as 'unique', 
    IF(source = 1, COUNT(userId), 0) as 'total'
FROM table
WHERE itemId = 1 AND userId = 1;
GROUP BY itemId;

Thanks in advance.

Upvotes: 0

Views: 83

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

use Conditional COUNT()

 SELECT itemId
      , COUNT( DISTINCT CASE WHEN source = 1
                             THEN userId
                        END)  as 'unique'
      , COUNT( CASE WHEN source = 1
                    THEN userId
               END)  as 'total'
FROM yourTable
WHERE itemId = 1 AND userId = 1
GROUP BY itemId

Upvotes: 1

Related Questions