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