Count Function with Month Convert

Question

Any help with adding the count funtion into the below query, counting on the id?

SELECT DISTINCT TOP 10 left(convert(varchar,B.CreatedDate,112),6) as CreatedDate_YYYYMM , B.id
FROM tbl B

Result set:

  CreatedDate_YYYYMM           id
    201201                     4
    201201                     1
    201202                     2
    201203                     3
    201203                     5

I would like to see a count of id per CreatedDate_YYYYMM:

  CreatedDate_YYYYMM         Count
  201201                     2
  201202                     1
  201203                     2

Answer 1

Assuming you want the top 10 based on count of distinct ids for each date range from highest to lowest.

SELECT TOP 10 left(convert(varchar,B.CreatedDate,112),6) as CreatedDate_YYYYMM
       , count(Distinct b.id) [Count]
FROM tbl B
GROUP BY left(convert(varchar,B.CreatedDate,112),6)           
ORDER BY count(*) desc

Though I would prefer to alias the column to something different as count is a reserved word.