Conditional group on 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 the group only in a year:

So:

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

I do not want to make 2 requests.

is there any way i can do with conditional group by here?

I can do with replacing the other, but I can not replace one with 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 will be better off with two separate requests, but you can do it like

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

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

Answer 2

You can use the over clause to return both one year and one month bill in one request:

 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 in SQL Fiddle.