CODEWITHSUNDEEP
mysqlsql
Vector JX
Vector JX

Reputation: 179

How to get count of each value against unique id in Mysql

I Have below mentioned table:

Where Val column has 3 distinct value a,b & c.

ID     Val
1      a
1      a
1      b
2      b
2      c
2      c
3      c

I want to get count of unique ids and count of ids for respective Val value (i.e a,b & c).

I am using query like this but it helps me to identify count for a single Val value at a time. 

SELECT ID,COUNT(*) 
FROM table1
WHERE Val='c' GROUP BY ID;

Required output:

ID      count    a     b    c
1       3        2     1    0
2       3        0     1    2      
3       1        0     0    1

Upvotes: 0

Views: 34

Answers (2)

jose_bacoy
jose_bacoy

Reputation: 12684

You can use group by and sum the count when val is equal to a,b or c. See below:

select id, 
    count(*) as `count`, 
    sum(case when val = 'a' then 1 else 0 end) as a, 
    sum(case when val = 'b' then 1 else 0 end) as b, 
    sum(case when val = 'c' then 1 else 0 end) as c
from yourTable
group by id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use conditional aggregation:

select id, count(*), sum(val = 'a') as a, sum(val = 'b') as b, sum(val = 'c') as c
from table1
group by id;

Upvotes: 1

Related Questions