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:

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

10 Answers

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.

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

6

Use the DATEPART function to extract the month from the date.

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)

6

I used the FORMAT function to accomplish this:

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

1

By Adding MONTH(date_column) in 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

0

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 -

0

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

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;

SQL Server 2012 version above,

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;

You can do this by using Year(), Month() Day() and 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

2

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;

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy