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().

GROUP BY MONTH([datetimecolumn])

TO group data by year you can use the in built funtion YEAR().

GROUP BY YEAR([datetimecolumn])

In order to group by month and year you can use bith YEAR() and MONTH().

GROUP BY YEAR([datetimecolumn])

Example code:

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 server 2005,2008,2008 r2,2012

(Visited 1,115 times, 1 visits today)



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: , , , , , , , , , , , , , , , ,