CODEWITHSUNDEEP
mysqlsql
DolDurma
DolDurma

Reputation: 17289

MySQl using COUNT with CASE statement

in my mysql database i have instagram_actions_histories table with two important column as action_type and action_name, now i want to use count and case statement to get count of ids column, for example:

count of all ids when action_type equal 1 and action_name equal 'like'

select `account_id`,

count(case when action_type = 1 and action_name='like' then id else 0 END) as `like`,
count(case when action_type = 1 and action_name='superLike' then id else 0 END) as `superLike`,
count(case when action_type = 2 then id else 0 END) as `follow`, 

from `instagram_actions_histories` 
where `instagram_actions_histories`.`account_id` in (1) 
group by `account_id`

unfortunately i get wrong result as all of outputs are same, for example:

account_id  like    superLike    follow
1           1282    1282         1282

correct result was should be:

account_id  like    superLike    follow
1           1282    20           10

Upvotes: 1

Views: 9679

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You should count 1 for a match, and count NULL when there is no match:

SELECT
    account_id,
    COUNT(CASE WHEN action_type = 1 AND action_name = 'like' THEN 1 END) AS `like`,
    COUNT(CASE WHEN action_type = 1 AND action_name = 'superLike' THEN 1 END) AS superLike,
    COUNT(CASE WHEN action_type = 2 THEN 1 END) AS follow
FROM instagram_actions_histories
WHERE account_id IN (1)
GROUP BY account_id;

The problem with the current logic of your CASE expressions is that COUNT will count any non null value as one count. So zero also would be counted.

Note that your current logic would have worked using SUM to take the conditional aggregations, e.g.

SUM(CASE WHEN action_type = 1 AND action_name = 'like'
         THEN 1 ELSE 0 END) AS `like`

In this case, to turn off the aggregation for non matching records, we in fact can use zero, because summing zero does not affect the sum.

Upvotes: 9

Related Questions