Conditional group by in sql server?

sql-server, sql-server-2005, t-sql

Solution

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.

Problem

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 ?

Original source