Group By Month, Year with example
Posted on : 02-05-2012 | By : Devi Prasad | In : SQL Server Basics, T-SQL Code Examples
0

In order to group by month you can use the SQL Server in built funtion Month().
[sql]
GROUP BY MONTH([datetimecolumn])
[/sql]
TO group data by year you can use the in built funtion YEAR().
[sql]
GROUP BY YEAR([datetimecolumn])
[/sql]
In order to group by month and year you can use bith YEAR() and MONTH().
[sql]
GROUP BY YEAR([datetimecolumn])
[/sql]
Example code:
[sql]
CREATE TABLE #Sales
(
Name VARCHAR (100),
SalesDateTime DATETIME
)
GO
INSERT INTO #Sales
SELECT ‘Product1’,
‘2010-04-01 00:00:00.000’
UNION ALL
SELECT ‘Product2’,
‘2010-04-02 00:00:00.000’
UNION ALL
SELECT ‘Product3’,
‘2011-04-02 00:00:00.000’
UNION ALL
SELECT ‘Product4’,
‘2011-04-03 00:00:00.000’
UNION ALL
SELECT ‘Product5’,
‘2011-04-03 00:00:00.000’
UNION ALL
SELECT ‘Product1’,
‘2012-04-30 00:00:00.000’
UNION ALL
SELECT ‘Product1’,
‘2012-04-30 00:00:00.000’
UNION ALL
SELECT ‘Product1’,
‘2012-05-02 00:00:00.000’
UNION ALL
SELECT ‘Product5’,
‘2012-05-02 00:00:00.000’
UNION ALL
SELECT ‘Product5’,
‘2012-05-02 00:00:00.000’
GO
–GROUP BY Month
SELECT count(*) AS SalesCount, MONTH(SalesDateTime) Month
FROM #Sales
GROUP BY MONTH(SalesDateTime)
–GROUP BY YEAR
SELECT count(*) AS SalesCount, YEAR(SalesDateTime) YEAR
FROM #Sales
GROUP BY YEAR(SalesDateTime)
–GROUP BY Month and Year
SELECT count(*) AS SalesCount, YEAR(SalesDateTime) YEAR,MONTH(SalesDateTime) Month
FROM #Sales
GROUP BY YEAR(SalesDateTime),MONTH(SalesDateTime)
GO
DROP TABLE #Sales
[/sql]
sql server 2005,2008,2008 r2,2012
Reference : Devi Prasad (sqlserverlearner.com)
Do you like my blog?
If you liked reading this blog, please help spread the word by sharing this blog with your friends.
Need Help On SQL Server?
Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.

Tags: group by month and year, group by month in sql server, group by month sql server 2000, group by month sql server 2005, group by month sql server 2008, group by month sql server 2008 r2, group by month sql server 2012, group by year and month, group by year in sql server, group by year sql server 2000, group by year sql server 2005, group by year sql server 2008, group by year sql server 2008 r2, group by year sql server 2012, how to group by month in sql server, how to group by year and month in sql server, how to group by year in sql server