如何使用 sql 从 Date 字段按月分组

如何从日期字段仅按月分组(而不按日分组) ？

下面是我的日期字段:

2012-05-01

下面是我当前的 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
428393 次浏览
小开

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

所以你会这样做:

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

可以通过使用 Year ()、 Month () Day ()和 datepart ()来实现这一点。

在你的例子中，这将是:

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
小开
最佳答案

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;

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

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

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

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

小开

我使用 格式函数来实现这一点:

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
小开

通过在 GROUP BY中添加 MONTH(date_column)。

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
小开

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

小开

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;
小开 
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;
小开

试试这个:

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

通过这种方式，您可以使用连接的日期格式进行分组，该格式由-加入

小开

上面的 SQLServer2012版本,

SELECT  format(Closing_Date,'yyyy-MM') as ClosingMonth,
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 format(Closing_Date,'yyyy-MM'), Category;

提交答案