Count matches after a left join
Your task
Return each title and its number of sale records, including unsold albums.
COUNT(column) skips NULL values. After a left join this can give zero for a parent with no matches.
Sample data
| id | title | genre | price | stock |
|---|---|---|---|---|
| 1 | Night Bus | Jazz | 18 | 7 |
| 2 | Static Bloom | Indie | 22 | 4 |
| 3 | Soft Circuit | Electronic | 24 | 0 |
| 4 | Blue Hour | Jazz | 16 | 3 |
| 5 | Coastline | Indie | 19 | 8 |
| 6 | Low Tide | Ambient | 21 | 2 |
Expected output
| title | COUNT(sales.id) |
|---|---|
| Night Bus | 2 |
| Static Bloom | 2 |
| Soft Circuit | 0 |
| Blue Hour | 1 |
| Coastline | 1 |
| Low Tide | 0 |
Show a hint
Count sales.id instead of counting every joined row.
Aggregates and reporting guide
Set the grain of a summary
Without GROUP BY, aggregates summarize the entire input. With GROUP BY genre, each result row represents one genre. Include a group key or an aggregate for each selected value so the meaning of the result is clear.
SELECT genre, COUNT(*) AS albums,
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM albums
GROUP BY genre;Filter rows or filter groups
WHERE runs before grouping. HAVING tests the resulting groups. Here sold-out albums cannot contribute to the counts or averages. Genres with fewer than two qualifying albums are dropped after grouping.
SELECT genre, COUNT(*) AS in_stock, AVG(price) AS average_price
FROM albums
WHERE stock > 0
GROUP BY genre
HAVING COUNT(*) >= 2;Count records at the right level
A LEFT JOIN leaves one row with a NULL sale ID for each unsold album. COUNT(*) would count that placeholder. COUNT(s.id) skips it and correctly shows zero sales for those albums.
SELECT a.title, COUNT(s.id) AS sale_records
FROM albums AS a
LEFT JOIN sales AS s ON s.album_id = a.id
GROUP BY a.id;Worked solution
SELECT albums.title, COUNT(sales.id) FROM albums LEFT JOIN sales ON sales.album_id = albums.id GROUP BY albums.id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
