Chris Storozuk Chris Storozuk - 5 years ago 145
SQL Question

SQL counting and grouping unique values

I need to count the number of occurrences of each "score" and group them by "make" according to the fake dataset below:

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:

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.

Answer Source
SELECT make, score, count(1)
FROM {your_table}
GROUP BY make,score
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download