CODEWITHSUNDEEP
sql
serkanz
serkanz

Reputation: 451

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

Upvotes: 1

Views: 63

Answers (3)

Anu
Anu

Reputation: 325

if you are using MSSQL

select DtlPivot.CardHolderID, isnull(DtlPivot.[0],0) as Deleted0, isnull(DtlPivot.[1],0) as Deleted1 from
(
    select c.CardHolderID, c.Deleted, COUNT(*) as Total from Card c 
    group by c.Deleted, c.CardHolderID
) aa
PIVOT
(
    sum(Total) FOR Deleted IN([0],[1])
)AS DtlPivot

Upvotes: 0

igr
igr

Reputation: 3499

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

Upvotes: 1

jarlh
jarlh

Reputation: 44766

GROUP BY CardHolderID alone.

Use SUM(Deleted) to count the 1's.

Use SUM(1-deleted) to count the 0's.

select c.CardHolderID, sum(1-c.deleted) deleted0, sum(c.Deleted) deleted1
from Card c 
group by c.CardHolderID

Upvotes: 1

Related Questions