如何在MySQL中按年份和月份分组 [英] How to Group By Year and Month in MySQL

问题描述

我想从源表中测量ID和ATTRIBUTE的计数，并显示数据，如下面的期望报告所示。

资料来源：

  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 
  

期望报表：

属性计数

  YEAR | JAN | FEB | MAR | APR | 5月| 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 
  

ID计数

  YEAR | JAN | FEB | MAR | APR | 5月| 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 
  

完成百分比（计数属性/计数ID ）

 年份| JAN | FEB | MAR | APR | 5月| 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  - 你的SQL语法错误;检查（CASE WHEN MONTH（document_filing_date）= 1 THEN aif_id END）作为Jan，CO'在第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）至于8月，
 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）; 
  

解决方案

此查询将计算所有行，只计算属性不为空的行，按行和年份分组：

  SELECT 
年（'date`），
月（`日期`），
计数（*）作为Total_Rows，
计数（`Attribute`） Rows_With_Attribute 
 FROM your_table 
 GROUP BY Year（`date`），Month（`date`）
  

（这是因为Count（*）计算了所有行，Count（Attibute）计算了Attribute不为null的所有行）

如果您需要表中的PIVOT，你可以用它来计算只有属性不为null的行：

  SELECT 
年份'date'），
 Count（月份（`date`）= 1，然后`Attribute`结束的情况）作为Jan，
 Count（月份（`date`）= 2的情况），则`Attribute`结束）作为2月，
计数（月份（`日期`）= 3，然后`属性`结束的情况下）作为Mar，
 ... 
 FROM your_table 
 GR OUP BY年份（'date`）
  

并且这个计算所有行：

  SELECT 
年（'日期'），
计数（月份（`日期`）= 1的情况） ）作为Jan，
 Count（月份（'date`）= 2，然后id结束的情况）作为Feb，
 Count（月份（'date'）= 3，然后id结束的情况） 
 ... 
 FROM your_table 
 GROUP BY年（`date`）
  

$ b $ （或者，可以使用 Sum（Month（ date ）= 1）像在坎德尔的回答中）。当然，您可以将这两个查询结合到这里：

  SELECT 
年份（'date`），
计数（月（'date'）= 1，然后id结束的情况）作为Jan_Tot，
 Count（月（'date'）= 1，然后`Attribute`结束的情况）作为Jan_Attr，
 Count当月（'日期'）= 2，然后id结束的情况下）作为Feb_Tot，
计数（月（'日期`）= 2，然后`属性`结束的情况下）作为Feb_Attr，
计数Mar_Tot，
 Count（月份（`date`）= 3，然后`Attribute`结束的情况）作为Mar_Attr，
 ... 
 FROM your_table 
 GROUP BY Year（`date`）
  

I would like to measure the count of ID and ATTRIBUTE from the source table and present the data as shown in the "Desired Report" below. I am using MySQL.

Source:

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

Desired Reports:

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 

Here's the code I have so far. Thanks! And Also keep in mind, I need to extract the Month from the date field in my data but not sure how. Thanks.

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);

Suggested answer doesn't work!! Not sure why, here is the error I get:

"#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);

解决方案

This query will count all the rows, and will also count just the rows where Attribute is not null, grouping by year and month in rows:

SELECT
  Year(`date`),
  Month(`date`),
  Count(*) As Total_Rows,
  Count(`Attribute`) As Rows_With_Attribute
FROM your_table
GROUP BY Year(`date`), Month(`date`)

(this because Count(*) counts all the rows, Count(Attibute) counts all the rows where Attribute is not null)

If you need your table in PIVOT, you can use this to count only the rows where Attribute is not null:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then `Attribute` end) As Jan,
  Count(case when month(`date`)=2 then `Attribute` end) As Feb,
  Count(case when month(`date`)=3 then `Attribute` end) As Mar,
  ...
FROM your_table
GROUP BY Year(`date`)

And this to count all the rows:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then id end) As Jan,
  Count(case when month(`date`)=2 then id end) As Feb,
  Count(case when month(`date`)=3 then id end) As Mar,
  ...
FROM your_table
GROUP BY Year(`date`)

(or, instead of counting id, you can use Sum(Month(date)=1) like in kander's answer). Of course you can combine both queries into this:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then id end) As Jan_Tot,
  Count(case when month(`date`)=1 then `Attribute` end) As Jan_Attr,
  Count(case when month(`date`)=2 then id end) As Feb_Tot,
  Count(case when month(`date`)=2 then `Attribute` end) As Feb_Attr,
  Count(case when month(`date`)=3 then id end) As Mar_Tot,
  Count(case when month(`date`)=3 then `Attribute` end) As Mar_Attr,
  ...
FROM your_table
GROUP BY Year(`date`)

这篇关于如何在MySQL中按年份和月份分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！