COUNT(DISTINCT ...) counts different values
Starting table: baristas
| name | cafe_id |
|---|---|
| Maya | 1 |
| Theo | 1 |
| Ren | 2 |
| Juni | 3 |
COUNT(DISTINCT column) tells you how many different values a column holds, setting repeats aside. Take a baristas table where Maya and Theo both work at café 1, Ren at café 2 and Juni at café 3: COUNT(*) gives 4, one per row, while COUNT(DISTINCT cafe_id) gives 3 unique values, because café 1 is included only once. Use COUNT(*) when you want rows, and COUNT(DISTINCT ...) when you want different values.
A tiny example
In plain English: Tell me how many different cafés our baristas work at.
SELECT COUNT(DISTINCT cafe_id)
FROM baristas;Query result
| COUNT(DISTINCT cafe_id) |
|---|
| 3 |
Four baristas work at three different cafés. cafe_id 1 appears twice but contributes only once, so the answer is 3, not 4.
Related SQL lessons
Ready for a break?
A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.
Try today's free SQL puzzle