如何计算mysql记录的每一个月的一年由一个字段分组 - how to count mysql records for each month of the year grouped by one field

how to count mysql records for each month of the year grouped by one field

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

recid | agency_id | departure_date

所以我需要按机构和月份(departure_date)计算"recid"组，并获得总列

  Agency_id  | JAN | FEB | MAR | APR | MAY | ........ | TOTAL 
      10        100  80    100   120   100    1200

这似乎很容易。但我找不到解决办法。任何帮助都将不胜感激!!

Try

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

Try

 select agency_id, 
    sum(if(month(`departure_date`) = 1,1,0)) as 'JAN',
    sum(if(month(`departure_date`) = 2,1,0)) as 'FEB',
    sum(if(month(`departure_date`) = 3,1,0)) as 'MAR',
    sum(if(month(`departure_date`) = 4,1,0)) as 'APR',
    sum(if(month(`departure_date`) = 5,1,0)) as 'MAY',
    sum(if(month(`departure_date`) = 6,1,0)) as 'JUN',
    sum(if(month(`departure_date`) = 7,1,0)) as 'JULY',
    sum(if(month(`departure_date`) = 8,1,0)) as 'AUG',
    sum(if(month(`departure_date`) = 9,1,0)) as 'SEPT',
    sum(if(month(`departure_date`) = 10,1,0)) as 'OCT',
    sum(if(month(`departure_date`) = 11,1,0)) as 'NOV',
    sum(if(month(`departure_date`) = 12,1,0)) as 'DEC',
            count(agency_id) as TOTAL 
 from tablename
 where .....
 group by agency_id

试一下:

SELECT 
    agency_id,
    SUM(IF(month = 1, numRecords, NULL)) AS 'January',
    SUM(IF(month = 2, numRecords, NULL)) AS 'Feburary',
    SUM(IF(month = 3, numRecords, NULL)) AS 'March',
    SUM(IF(month = 4, numRecords, NULL)) AS 'April',
    SUM(IF(month = 5, numRecords, NULL)) AS 'May',
    SUM(IF(month = 6, numRecords, NULL)) AS 'June',
    SUM(IF(month = 7, numRecords, NULL)) AS 'July',
    SUM(IF(month = 8, numRecords, NULL)) AS 'August',
    SUM(IF(month = 9, numRecords, NULL)) AS 'September',
    SUM(IF(month = 10, numRecords, NULL)) AS 'October',
    SUM(IF(month = 11, numRecords, NULL)) AS 'November',
    SUM(IF(month = 12, numRecords, NULL)) AS 'December',
    SUM(numRecords) AS total
    FROM (
        SELECT agency_id, month(departure_date) AS month, count(*) as numRecords
        FROM your_table_name
        GROUP BY agency_id, month
    ) AS SubTable1 GROUP BY agency_id

对于没有特定agency_id记录的月份，该查询将显示null。如果希望显示为0，请更新查询并将NULL替换为文字0。

看一下这个ROLLUP子句的解决方案，它显示了您需要的内容，但给出了另一个输出-

SELECT
  agency_id, MONTH(departure_date) month, COUNT(*) count
FROM
  sales
GROUP BY
  agency_id, MONTH(departure_date) WITH ROLLUP
WHERE
  YEAR(departure_date) = 2013