SQL: count occurrences of values

Question

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 1

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

Answer 2

If you´re working on an Oracle, you would use the PIVOT-function:

SELECT *
  FROM fruit t
 PIVOT (COUNT(fruit) AS cnt 
          FOR(fruit) IN ('apple' AS apple
                       , 'pear' AS pear) );

More details and full samples on PIVOT / UNPIVOT you´ll find in the web (f.e. here https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 )