SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 43 of 250

Moderate

Aggregate conditionally

Your task

Return the number of sold-out albums and the number of stocked albums in a single row, in that order.

An aggregate can consume the output of CASE. A one-or-zero expression counts rows matching a condition.

Sample data

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

Expected output

SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END)SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END)
15
Show a hint

Use two SUM(CASE WHEN ... THEN 1 ELSE 0 END) expressions.

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…