Build a sales report
Your task
Return every album's title and total copies sold, including zero for unsold albums. Sort by total sold descending, then by title alphabetically.
You can join tables, group the joined rows, and sort by an aggregate in one query. COALESCE replaces a NULL result with a fallback, such as zero for an album with no sales.
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 | copies_sold |
|---|---|
| Night Bus | 5 |
| Static Bloom | 3 |
| Blue Hour | 2 |
| Coastline | 1 |
| Low Tide | 0 |
| Soft Circuit | 0 |
Show a hint
LEFT JOIN sales, group by albums.id, and use COALESCE(SUM(sales.quantity), 0). Order by the total and then albums.title.
SQL fundamentals guide
Read a table
A table has named columns and one row per record. In albums, id identifies a row, title describes it, and stock is how many copies the shop has. Start by selecting just the columns you need. The result below shows titles beside their current stock.
SELECT title, stock
FROM albums;Filter, then sort
WHERE keeps rows that satisfy its condition. The next query only considers albums in stock. ORDER BY applies to those matches, and LIMIT returns the first three. The second sort key makes the result predictable if prices tie.
SELECT title, price
FROM albums
WHERE stock > 0
ORDER BY price DESC, id
LIMIT 3;Think about what one row represents
One album can have several sale records. Joining albums to sales makes each result row represent one sale, so an album title may repeat. Grouping by album ID brings the result back to one row per album that has sold.
SELECT a.title, SUM(s.quantity) AS copies_sold
FROM albums AS a
JOIN sales AS s ON s.album_id = a.id
GROUP BY a.id;Worked solution
SELECT albums.title, COALESCE(SUM(sales.quantity), 0) AS copies_sold FROM albums LEFT JOIN sales ON sales.album_id = albums.id GROUP BY albums.id ORDER BY copies_sold DESC, albums.title;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
