SQL:计算值的出现次数

Question

让

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

尝试合并 count和 group by要得到

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

任何有关如何进行的提示表示赞赏。

Answer 1

使用 case进行条件计数的表达式:

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