How to group by month from Date field using sql
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
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).
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)
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
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
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 -