Filter before grouping
Your task
Return the genre and number of stocked albums in each genre that has stocked albums.
WHERE removes input rows before GROUP BY forms groups.
Sample data
albums| 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
| genre | COUNT(*) |
|---|---|
| Ambient | 1 |
| Indie | 2 |
| Jazz | 2 |
Show a hint
Use WHERE stock > 0 before GROUP BY.
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 genre, COUNT(*) FROM albums WHERE stock > 0 GROUP BY genre;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
