3 · Summarize and connect · 12 MIN

Build a grouped report and filter groups

GROUP BY changes the result grain from one learner to one city.

A report’s grain tells you what one output row represents. Grouping by city creates one output row per city value, including one NULL group. COUNT(*) and SUM(points) are calculated separately inside each group. WHERE filters individual rows before grouping; HAVING filters the resulting groups. Do not select a learner name while grouping only by city: one city can contain several names and there is no single correct name for that output row.

Keep the source rows visible. Predict which rows and columns will remain after each operation.

Before you start

No installation. The tables below are loaded automatically for every run; edits do not touch the CodingNeed account database.

New words, explained

grain: What a single result row represents.
GROUPBY: Form groups sharing selected values.
HAVING: Filter groups after aggregate values are available.

Follow the example step by step

Write the target grain: one row per city.
Place Ada and Grace in the same Delhi group.
Aggregate each group, then apply HAVING.
Exclude missing cities with WHERE before grouping in the exercise.

Practice table: learners
id	name	city	points
1	Ada	Delhi	20
2	Lin	NULL (unknown)	0
3	Grace	Delhi	35
4	Sam	Pune	20

Practice table: enrollments
learner_id	course
1	React
1	SQL
3	SQL

You are ready to move on when: Return city and SUM(points) for known cities only. Keep groups totaling at least 30 points, and order by city.

Read the example

SELECT COALESCE(city, 'Unknown'), COUNT(*), SUM(points)
FROM learners
GROUP BY city
ORDER BY COALESCE(city, 'Unknown');

Check the expected output

[["Delhi",2,55],["Pune",1,20],["Unknown",1,0]]

Your challenge

Return city and SUM(points) for known cities only. Keep groups totaling at least 30 points, and order by city.

Common trap

Putting an aggregate condition in WHERE confuses row filtering with group filtering.

Open interactive exercise →See the syllabus

Further reading: SQLite SELECT reference

Next lesson: Project · Join learners to enrollment counts →