mysql怎么按年份分组_如何按年份和月份在MySQL中进行分组

我想从源表中测量ID和ATTRIBUTE的计数，并显示如下所示的“所需报告”中的数据。我正在使用MySQL。

资源：

ID | DATE | ATTRIBUTE

--------------------------------

1 | 2012-01-14 | XYZ

2 | 2012-03-14 |

3 | 2012-03-15 | XYZ

4 | 2012-04-24 | ABC

5 | 2012-04-10 |

6 | 2012-05-11 | ABC

所需报告：

Count of Attribute

YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC

---------------------------------------------------------------------------

2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

Count of ID

YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC

---------------------------------------------------------------------------

2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

Percentage Complete ( Count of Attribute / Count of ID )

YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC

---------------------------------------------------------------------------

2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

这是我迄今为止的代码。谢谢！还要记住，我需要从数据中的日期字段中提取月份，但不知道如何。谢谢。

SELECT YEAR(document_filing_date),MONTH(document_filing_date),COUNT(aif_id)

FROM (a_aif_remaining)

GROUP BY YEAR(document_filing_date),MONTH(document_filing_date);

建议的答案不行！不知道为什么，这里是我得到的错误：

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' COUNT(CASE WHEN MONTH(document_filing_date) = 1 THEN aif_id END) AS Jan, CO' at line 1"

SELECT YEAR(document_filing_date,

COUNT(CASE WHEN MONTH(document_filing_date) = 1 THEN aif_id END) AS Jan,

COUNT(CASE WHEN MONTH(document_filing_date) = 2 THEN aif_id END) AS Feb,

COUNT(CASE WHEN MONTH(document_filing_date) = 3 THEN aif_id END) AS Mar,

COUNT(CASE WHEN MONTH(document_filing_date) = 4 THEN aif_id END) AS Apr,

COUNT(CASE WHEN MONTH(document_filing_date) = 5 THEN aif_id END) AS May,

COUNT(CASE WHEN MONTH(document_filing_date) = 6 THEN aif_id END) AS Jun,

COUNT(CASE WHEN MONTH(document_filing_date) = 7 THEN aif_id END) AS Jul,

COUNT(CASE WHEN MONTH(document_filing_date) = 8 THEN aif_id END) AS Aug,

COUNT(CASE WHEN MONTH(document_filing_date) = 9 THEN aif_id END) AS Sep,

COUNT(CASE WHEN MONTH(document_filing_date) = 10 THEN aif_id END) AS Oct,

COUNT(CASE WHEN MONTH(document_filing_date) = 11 THEN aif_id END) AS Nov,

COUNT(CASE WHEN MONTH(document_filing_date) = 12 THEN aif_id END) AS Dec,

FROM a_aif_remaining

GROUP BY YEAR(document_filing_date);