SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 48 of 250

Moderate

Order a summary

Your task

Return each genre and average price, highest average first. Break ties by genre alphabetically.

An alias from the SELECT list can be reused in ORDER BY in SQLite.

Sample data

Table:albums
idtitlegenrepricestock
1Night BusJazz187
2Static BloomIndie224
3Soft CircuitElectronic240
4Blue HourJazz163
5CoastlineIndie198
6Low TideAmbient212

Expected output

genreavg_price
Electronic24
Ambient21
Indie20.5
Jazz17
Show a hint

Name AVG(price) and order by the alias descending, then by genre.

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…