Group By Month, Year with example

Posted on : 02-05-2012 | By : Devi Prasad | In : SQL Server Basics, T-SQL Code Examples

Share:

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




References : Devi Prasad (sqlserverlearner.com)

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.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , , , , , , , , , ,