SQL Server: Group By Year, Month and Day

I have seen some confusion in developers as how to do a Group by Year, Month or Day, the right way. Well to start, here’s a thumb rule to follow:

Do not use GROUP BY MONTH or GROUP BY DAY Individually

Developers often write the following statement

The problem with the above statement is that if the table contains data spanning more than one year, a data consolidation occurs which is not what you desire. For eg: July 2007 and July 2008 are both treated as 7 (7th month) and the data is consolidated. The same occurs for all other months too and you get only 12 rows of data.

GROUP BY MONTH or DAY – Right way

The right way to GROUP BY MONTH or DAY is to add YEAR to the GROUP BY clause. This way data will not be consolidated. For eg: now July 2007 and July 2008 are treated separate as a GROUP BY YEAR has been added. Here’s the same query with the GROUP BY YEAR clause

USE Northwind
GO
SELECT COUNT(*) as TotalOrders,
YEAR(OrderDate) as OrderYear,
MONTH(OrderDate) as OrderMonth
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

OUTPUT

As you can see, the query generates 23 rows now with data for each month/year grouped separately

Note: For some reason, if you do not want to GROUP BY YEAR or MONTH separately, you can also use DATEDIFF/DATEADD operations as shown below:

USE Northwind
GO
SELECT COUNT(*) as TotalOrders,
DATEADD(month, DATEDIFF(month, 0, OrderDate),0) as OrderDate
FROM Orders
GROUP BY DATEADD(month, DATEDIFF(month, 0, OrderDate),0)
ORDER BY OrderDate

OUTPUT

The above query generates 23 rows and rounds off to the first day in each month.

Also read my article Count SubTotals and Totals using ROLLUP in SQL Server and Group and Count Records in SQL Server

Reference: Jeff Smith Blog

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook