CODEWITHSUNDEEP
mysql
joe
joe

Reputation: 13

MYSQL counting returns nothing for a where statement

I have a table of the form:

user_id |  categories
---------------------
   1    | ['apple','orange']
   2    | ['apple','banana']
   1    | ['apple','squash']
   3    | ['fig']

I want to summarize the count of 'apple' for each user to give:

user_id |  apple_count
---------------------
   1    | 2
   2    | 1
   3    | 0

So I thought it would be something like:

SELECT user_id, Count(categories) AS apple_count 
    FROM table 
       WHERE categories LIKE     
       '%apple%' 
       GROUP BY user_id;

which unfortunately returns:

user_id |  apple_count
---------------------
   1    | 2
   2    | 1

I was thinking maybe I can solve this with a left-join on all unique user id's, and append 0's to them? But what would be an elegant way of doing this?

Upvotes: 1

Views: 32

Answers (1)

Rahul
Rahul

Reputation: 77896

You are not getting the last part because of the WHERE condition. Change your query a little using a CASE statement and include the condition in an aggregate function itself like below:

SELECT user_id, 
SUM(CASE WHEN categories LIKE '%apple%' THEN 1 ELSE 0 END) AS apple_count 
FROM table 
GROUP BY user_id;

Upvotes: 1

Related Questions