TSQL Count Group By Year and Month

SQLSQL Server

Please contribute by voting. Thanks!
3

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 '18. Edited Feb 7 '18.

Your comment

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

Author

Created by Matt Hamilton [13] Feb 7 '18

Share article

Do you know about

ReSharper?

Write an article