mysql - 获取列中的计数

Question

我想要一个查询，给出重复的区域计数，其中日期为 'april2018' 预期的查询输出

显示的表格

+---------+--------------+
| region  |     Date     |
+---------+--------------+
| africa  |    april2018 |
| africa  |    may2018   |
| africa  |    may2018   |
| america |   jan2018    |
| america |   jan2018    |
| africa  |    april2018 |
| africa  |    may2018   |
| africa  |    may2018   |
| india   |    jan2018   |
| india   |    jan2018   |
+---------+--------------+    

预期结果

+----------+---------+
|  region  |  Count  |
+----------+---------+
|  africa  |       2 |
|  america |       0 |
|  india   |       0 |
+----------+---------+

标签： mysqlsqldatabase

Answer 1

case when与计数一起使用

select region, coalesce(count(case when Date = 'april2018' then 1 end),0) as count
from tablname
group by region