Summarize sale sizes by album
Your task
For sold albums, return album_id, the smallest sale quantity as smallest_sale, and the largest as largest_sale. Sort by album ID.
Several aggregate expressions can describe the same group of records.
Sample data
sales| id | album_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 102 | 2 | 1 |
| 103 | 1 | 3 |
| 104 | 4 | 2 |
| 105 | 5 | 1 |
| 106 | 2 | 2 |
Expected output
| album_id | smallest_sale | largest_sale |
|---|---|---|
| 1 | 2 | 3 |
| 2 | 1 | 2 |
| 4 | 2 | 2 |
| 5 | 1 | 1 |
Show a hint
Use MIN and MAX of quantity in the same grouped query.
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 album_id, MIN(quantity) AS smallest_sale, MAX(quantity) AS largest_sale FROM sales GROUP BY album_id ORDER BY album_id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
