I need to count the number of occurrences of each "score" and group them by "make" according to the fake dataset below:
我需要计算每个“得分”的出现次数，并根据下面的假数据集将它们按“make”分组：
make | model | score Honda Accord 9 Honda CRV 9 Honda Prelude 8 Toyota Camry 8 Toyota Tundra 8 Ford F150 7 Ford Tempo 7 Ford Focus 8
This is what I need - 1 row for each make and score combination:
这就是我需要的 - 每个制作和得分组合1行：
make | score | count Honda 9 2 Honda 8 1 Toyota 8 2 Ford 7 2 Ford 8 1
I'm not concerned with order ATM. I've tried several versions using distincts but can't seem to get the desired output.
我不关心订单ATM。我已尝试使用区别的几个版本，但似乎无法获得所需的输出。
Followup: I'd like to add rows to the output where a Make has a count of 0 (none) for any possible score value (in this case its {7, 8, 9}).
后续：我想在输出中添加行，其中Make对任何可能的分数值（在这种情况下为{7,8,9}）的计数为0（无）。
The output would change to:
输出将更改为：
make | score | count Honda 9 2 Honda 8 1 Honda 7 0 Toyota 9 0 Toyota 8 2 Toyota 7 0 Ford 9 0 Ford 8 1 Ford 7 2
1 个解决方案
#1
0
SELECT make, score, count(1)
FROM {your_table}
GROUP BY make,score
#1
0
SELECT make, score, count(1)
FROM {your_table}
GROUP BY make,score