echo echo - 7 months ago 12
SQL Question

SQL: count occurrences of values

Let

user | fruit
------------
1 | apple
1 | apple
1 | apple
2 | apple
2 | apple
1 | pear


Trying to combine
count
and
group by
to get

user | apples | pears
---------------------
1 | 3 | 1
2 | 2 | 0


Any hints on how to proceed are appreciated.

Answer

Use case expressions to do conditional counting:

select user,
       count(case when fruit = 'apple' then 1 end) as apples,
       count(case when fruit = 'pear' then 1 end) as pears
from tablename
group by user
Comments