php - 如何统计一年中每个月的mysql记录按一个字段分组

Question

我一直在查看一些示例，但没有找到我需要的。该查询显示按月分组的每个机构的记录数。这是我的表结构的一部分:

recid | agency_id | departure_date 

所以我需要按机构和月份(出发日期)计算“recid”组并获取总列

  Agency_id  | JAN | FEB | MAR | APR | MAY | ........ | TOTAL 

      10        100  80    100   120   100    1200   

看起来很简单。但我找不到解决方案。任何帮助将不胜感激!!!

Answer 1

尝试

SELECT agency_id, 
       SUM(CASE WHEN MONTH(departure_date) = 1 THEN 1 ELSE 0 END) Jan,
       SUM(CASE WHEN MONTH(departure_date) = 2 THEN 1 ELSE 0 END) Feb,
       SUM(CASE WHEN MONTH(departure_date) = 3 THEN 1 ELSE 0 END) Mar,
       ...
       COUNT(*) Total
  FROM table1
 WHERE departure_date BETWEEN '2013-01-01' AND '2013-12-31'
 GROUP BY agency_id

输出:

| AGENCY_ID | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | TOTAL |
---------------------------------------------------------------------------------------------
|         1 |   1 |   1 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |     3 |
|         2 |   2 |   2 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |     5 |
|         3 |   0 |   0 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |     2 |

<强> SQLFiddle