分组时如何根据条件求和

Question

我有一个行列表，我想在分组时对行数进行求和（一种 COUNT），但仅当每行字段 customField=0 时才进行求和。

示例：

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

那么，输出应该是：

aaa 2
bbb 1
ccc 0

How can I do it with MySql?

编辑

事实上，我真正的查询是这个：

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

只有当 forum_messages.original=0 时，我才必须计算 COUNT(forum_messages.id) AS Total_replies >，这就是我请求 SUM 的原因:)

Answer 1

只需过滤结果集以排除 customField=1，即

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

结果：

aaa 2
bbb 1

显示所有标题，包括那些计数为 0 的标题：（如果需要其他计数也很有帮助）

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

结果：

aaa 2
bbb 1
ccc 0

更紧凑（但有效）仅在 MYSQL 中）：

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