SQL chapters

Chapter 10 of 17

Aggregates and grouping

Try this in our SQLite Compiler →

The idea

GROUP BY creates groups of matching keys and aggregates summarise each group. WHERE filters input rows before grouping; HAVING filters completed groups. count(*) counts rows, while count(column) excludes NULL values in that column.

Worked example

WITH scores(topic, score) AS (VALUES ('SQL', 80), ('SQL', 60), ('HTML', 90))
SELECT topic, count(*) AS attempts, avg(score) AS average FROM scores GROUP BY topic ORDER BY topic;

What to check

HTML has one attempt averaging 90; SQL has two averaging 70. Do not select an unrelated ungrouped column and assume it represents the group.

Practice task

Add HAVING count(*) >= 2 and predict which topic remains. Then add a NULL score and compare the two count forms.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on