let's say there's a table have data like below

I want find count of id of each month by their status. Something like this below

I tried doing

-- select distinct (not working)

select date, status1, status2 from 
(select date, count(id) as "status1" from myTable
where status = 4 group by date) as myTable1
join
(select date, count(id) as "status2" from myTable
where status = 3 group by date) as myTable2
on myTable1.date = myTable2.date;

-- group by (not working)

but it does duplicate the data needed. and I am using SQL Server.

CodePudding user response：

select d.date,
 sum
 (
    case 
      when d.status=4 then 1
      else 0
    end
 )count_status_4,
 sum
 (
    case 
      when d.status=5 then 1
      else 0
    end
 )count_status_5
from your_table as d
group by d.date