SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 39 of 250

Easy

Count unique values

Your task

Return the number of different genres.

COUNT(DISTINCT column) counts the distinct non-NULL values in a column.

Sample data

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

Expected output

COUNT(DISTINCT genre)
4
Show a hint

Put DISTINCT inside COUNT.

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…