Conditional group by in sql server?

Question

i have this simple query :

SELECT YEAR(P.DateCreated)
      ,MONTH(P.DateCreated)
      ,COUNT(*) AS cnt
FROM   tbl1,
       tbl2....
GROUP BY
         MONTH(P.DateCreated)
        ,YEAR(P.DateCreated)

this will emit :

now i need the same query but with groupby only per year :

so :

SELECT YEAR(P.DateCreated)

      ,COUNT(*) AS cnt
FROM   tbl1,
       tbl2....
GROUP BY
         YEAR(P.DateCreated)

i dont want to make 2 queries.

is there any way i can do conitional group by here ?

i can do with one being replaced by another , but i cant do one being replaced by two...

GROUP BY
     CASE WHEN @timeMode='y' THEN YEAR(P.DateCreated)
          WHEN @timeMode='m' THEN MONTH(P.DateCreated), YEAR(P.DateCreated) end

any help ?

Answer 1

You would be better off with two separate queries but can do it like

GROUP BY YEAR(P.DateCreated),
       CASE 
          WHEN @timeMode='m' THEN MONTH(P.DateCreated) end

As WHEN @timeMode <> 'm' the second GROUP BY expression will be NULL for all rows and not affect the result.

Answer 2

You could use an over clause to return both the per-yearly and the per-month count in one query:

SELECT  distinct YEAR(P.DateCreated) as Year
,       MONTH(P.DateCreated) as Month
,       COUNT(*) over (partition by YEAR(P.DateCreated), MONTH(P.DateCreated)) 
            as MonthCount
,       COUNT(*) over (partition by YEAR(P.DateCreated)) as YearCount
FROM   YourTable P

Live example at SQL Fiddle.