CODEWITHSUNDEEP
mysqlsqloracle-databasecount
user862833
user862833

Reputation: 309

How to get count of unique values in a column group by the primary key

I am new to sql and i have this problem in hand. I have a table temp which has id and flag as its columns.

ID FLAG               
-- ---- 
A    1                      
A    1                      
A    0                      
B    1                      
B    0                      
B    0                      
C    0                      
C    0                      
C    0

I need the 1's and 0's count with respect to each ID.

The desired output is 

ID    OnesCount    ZerosCount
---   ---------    ----------
A       2            1
B       1            2
C       0            3

I tried a lot i can get them individually by

select id,count(*) ZerosCount from temp where flag = 0 group by id

select id,count(*) OnesCount from temp where flag = 1 group by id

But do not understand how to join and get the desired output. Can some one please help

Upvotes: 0

Views: 490

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

select customer_id, 
   count(case when pwr_flag = 0 then 1 end) ZerosCount,
   count(case when pwr_flag = 1 then 1 end) OnessCount 
from temp_pwr
group by customer_id

Upvotes: 0

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

In this specific case you can do like this:

select customer_id ID, 
       sum(pwr_flag) OnesCount, 
       sum(1-pwr_flag) ZerosCount 
  from temp_pwr 
 group by customer_id

In a more generic case you can use case when:

select customer_id ID, 
       sum(case pwr_flag when 1 then 1 else 0 end) OnesCount, 
       sum(case pwr_flag when 0 then 1 else 0 end) ZerosCount 
       sum(case pwr_flag when 17 then 1 else 0 end) SeventeensCount 
  from temp_pwr 
 group by customer_id

Upvotes: 2

Related Questions