Count rows according to 2 column with Group By

I have a database table of 3 columns

RecordID    Deleted CardHolderID
1963        1       9
4601        1       9
6996        0       9
1532        1      11
1529        0      20

I want an sql query to output the sum of rows of Deleted column grouped by CardHolderID.

However query below outputs 2 columns for CardHolderID

select c.CardHolderID, c.Deleted, COUNT(*) as Sum 
from Card c 
group by c.Deleted, c.CardHolderID

CardHolderID    Deleted Sum
9               0       1
9               1       2
20              0       1
11              1       1

I want to include 2 columns as Deleted0 (count of rows with Deleted column equal to 0) and Deleted1 (count of rows with Deleted column equal to 1)

CardHolderID    Deleted0 Deleted1
9               1       2
20              1       0
11              1       1

How should be the SQL query for such a result?

Kind regards

Answer

Using conditional count:

select c.CardHolderID, 
   count( case when c.deleted > 0 then 1 else null end ) deleted0, 
   count( case when c.deleted = 0 then 1 else null end ) deleted1, 
from Card c 
group by c.CardHolderID

Advertisement

Answer