SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 52 of 250

Moderate

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

Table:sales
idalbum_idquantity
10112
10221
10313
10442
10551
10622

Expected output

album_idsmallest_salelargest_sale
123
212
422
511
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;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…