February 14, 2011

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

GROUP BY MONTH

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

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

Group By Year Month

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

Group By DateDiff

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "SQL Server: Group By Year, Month and Day"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions