TSQL Count Group By Year and Month

SQLSQL Server

Please contribute by voting. Thanks!
1

So it's pretty common that you need to group some data in SQL Server by Year and Month in order to see how many records that exist in each period. The date-field is typically datetime or datetimeoffset. Here's an easy method to achieve this without having to repeat the extraction of year and month:

SELECT YearMonth, COUNT(*) FROM (
    SELECT CAST(YEAR(createdate) AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(MONTH(createdate) AS VARCHAR(2)), 2) as YearMonth FROM [dbo].[SomeTable] 
) InnerQuery
GROUP BY YearMonth
ORDER BY YearMonth DESC

Article created: Feb 7 at 10:42. Edited Feb 7 at 10:43.

Your comment

You need to sign up / log in to comment this article

Author

Created by Matt Hamilton [8] Feb 7 at 10:42

Share article

Do you know about

Debugging?

Write an article