Mysql group by 计数有不同的逻辑

Question

我有一个包含三个字段的表:.

field1 - field2 - field3

field1 is a id;
field2 is the date;
field3 only has two possible values ("ac" and "re").

我需要知道是否可以使用 group by 进行查询，这将为我提供最后显示的结果:

select field2
       , count(total per date)
       , Count(where field3 = "re") 
  from myTable
 group by 1;

如果我的 table 是:

field1 - field2 - field3
=========================
 item1 - date1  - "ac"
 item2 - date1  - "re"
 item3 - date1  - "re"
 item4 - date2  - "re"
 item5 - date2  - "ac"
 item6 - date3  - "ac"

结果将是这样的:

 - date1 - 3 - 2
 - date2 - 2 - 1
 - date3 - 1 - 0

Answer 1

SELECT field2, COUNT(*), SUM(CASE WHEN field3='re' THEN 1 ELSE 0 END) as re_count
FROM table1
GROUP BY field2