CODEWITHSUNDEEP
mysqlsqlaggregate-functions
kwichz
kwichz

Reputation: 2453

How to SUM with condition when grouping

I have a list of rows, and I'd like to SUM the number of the lines when grouping (a sort of COUNT) but only when, for each row, the field customField=0.

Example :

title       customField
aaa         1
aaa         0
bbb         0
ccc         1
bbb         1
aaa         0

So, the output should be :

aaa 2
bbb 1
ccc 0

How can I do it with MySql?

EDIT

In fact my real Query is this one :

SELECT forum_categories.title, COUNT(forum_topics.id) AS total_topics, COUNT(forum_messages.id) AS total_replies, MAX(forum_messages.date) AS last_message
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id = forum_categories.id 
JOIN forum_messages ON forum_messages.topic_id = forum_topics.id
GROUP BY forum_categories.id ORDER BY forum_categories.date

And I have to count COUNT(forum_messages.id) AS total_replies only when forum_messages.original=0, that's why I request the SUM :)

Upvotes: 1

Views: 1139

Answers (1)

Ryan
Ryan

Reputation: 28247

Just filter the result set to exclude customField=1, i.e.

  SELECT title, COUNT(*) AS numTitle
    FROM yourtable
   WHERE customField = 0
GROUP BY title
ORDER BY numTitle DESC

Result:

aaa 2
bbb 1

To show all titles, including those which have a 0 count: (also helpful if other countings are needed)

  SELECT title
       , SUM(CASE WHEN customField=0 THEN 1 ELSE 0 END) AS numTitle
    FROM yourtable
GROUP BY title
ORDER BY numTitle DESC

Result:

aaa 2
bbb 1
ccc 0

More compact (but works only in MYSQL):

  SELECT title
       , SUM(customField=0) AS numTitle
    FROM yourtable
GROUP BY title
ORDER BY numTitle DESC

Upvotes: 7

Related Questions