SQL 如何使用sql从日期字段按月分组

How to group by month from Date field using sql

提问by user1858332

How can I group only by month from a date field (and not group by day)?

如何从日期字段中仅按月分组（而不是按天分组）？

Here is what my date field looks like:

这是我的日期字段的样子：

2012-05-01

Here is my current SQL:

这是我当前的 SQL：

select  Closing_Date, Category,  COUNT(Status)TotalCount from  MyTable
where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31'
and Defect_Status1 is not null
group by  Closing_Date, Category

回答by GarethD

I would use this:

我会用这个：

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;

This will group by the first of every month, so

这将在每个月的第一天分组，所以

`DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130128'), 0)` 

will give '20130101'. I generally prefer this method as it keeps dates as dates.

会给'20130101'。我通常更喜欢这种方法，因为它将日期保留为日期。

Alternatively you could use something like this:

或者你可以使用这样的东西：

SELECT  Closing_Year = DATEPART(YEAR, Closing_Date),
        Closing_Month = DATEPART(MONTH, Closing_Date),
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEPART(YEAR, Closing_Date), DATEPART(MONTH, Closing_Date), Category;

It really depends what your desired output is. (Closing Year is not necessary in your example, but if the date range crosses a year boundary it may be).

这真的取决于你想要的输出是什么。（在您的示例中不需要关闭年份，但如果日期范围跨越年份边界，则可能需要）。

回答by Bogdan Gavril MSFT

Use the DATEPARTfunction to extract the month from the date.

使用DATEPART函数从日期中提取月份。

So you would do something like this:

所以你会做这样的事情：

SELECT DATEPART(month, Closing_Date) AS Closing_Month, COUNT(Status) AS TotalCount
FROM t
GROUP BY DATEPART(month, Closing_Date)

回答by Andrei Sura

I used the FORMATfunction to accomplish this:

我使用FORMAT函数来完成此操作：

select
 FORMAT(Closing_Date, 'yyyy_MM') AS Closing_Month
 , count(*) cc 
FROM
 MyTable
WHERE
 Defect_Status1 IS NOT NULL
 AND Closing_Date >= '2011-12-01'
 AND Closing_Date < '2016-07-01' 
GROUP BY FORMAT(Closing_Date, 'yyyy_MM')
ORDER BY Closing_Month

回答by Aniket Warey

By Adding MONTH(date_column)in GROUP BY.

通过增加MONTH(date_column)在GROUP BY。

SELECT Closing_Date, Category,  COUNT(Status)TotalCount
FROM   MyTable
WHERE  Closing_Date >= '2012-02-01' AND Closing_Date <= '2012-12-31'
AND    Defect_Status1 IS NOT NULL
GROUP BY MONTH(Closing_Date), Category

回答by Jordan

DATEPART function doesn't work on MySQL 5.6, instead use MONTH('2018-01-01')

DATEPART 函数在 MySQL 5.6 上不起作用，而是使用 MONTH('2018-01-01')

回答by Nida

SELECT  to_char(Closing_Date,'MM'), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY Category;

回答by John Sonnino

Try this:

尝试这个：

select min(closing_date), date_part('month',closing_date) || '-' || date_part('year',closing_date) AS month,
Category, COUNT(Status)TotalCount 
FROM MyTable
where Closing_Date >= '2012-02-01' AND Closing_Date <= '2012-12-31'
AND Defect_Status1 is not null
GROUP BY month, Category,
ORDER BY 1

This way you are grouping by a concatenated date format, joined by a -

通过这种方式，您可以按连接日期格式进行分组，并由 -

回答by user1845584

You can do this by using Year(), Month() Day() and datepart().

您可以通过使用 Year()、Month()、Day() 和 datepart() 来完成此操作。

In you example this would be:

在你的例子中，这将是：

select  Closing_Date, Category,  COUNT(Status)TotalCount from  MyTable
where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31' 
and Defect_Status1 is not null 
group by Year(Closing_Date), Month(Closing_Date), Category

回答by Antony raj

Try the Following Code

试试下面的代码

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;