2 · Handle real values · 12 MIN
Count rows before grouping them
An aggregate summarizes several rows into a single value.
COUNT(*) counts rows, including a row whose city is missing. COUNT(city) counts only non-NULL city values. SUM(points) adds numeric scores. With no GROUP BY these aggregates produce one result row for the entire filtered input. SUM over no rows returns NULL, while COUNT returns zero; COALESCE can make a documented zero-total rule explicit. Column aliases such as learner_count make report columns readable without changing the stored table.
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
- aggregate
- An operation combining many rows into one result.
- alias
- A display name assigned with AS.
- emptySet
- No rows satisfy the filter.
Follow the example step by step
- Count all rows, then count known cities.
- Add 20 + 0 + 35 + 20 to obtain 75.
- Filter to an empty input and compare COUNT with SUM.
- Document why this exercise chooses zero for an empty total.
| id | name | city | points |
|---|---|---|---|
| 1 | Ada | Delhi | 20 |
| 2 | Lin | NULL (unknown) | 0 |
| 3 | Grace | Delhi | 35 |
| 4 | Sam | Pune | 20 |
| learner_id | course |
|---|---|
| 1 | React |
| 1 | SQL |
| 3 | SQL |
You are ready to move on when: Return one row containing COUNT(*) and COALESCE(SUM(points), 0) for learners with points > 100.
Read the example
SELECT COUNT(*) AS learner_count, COUNT(city) AS known_cities, SUM(points) AS total_points FROM learners;
Check the expected output
[[4,3,75]]
Your challenge
Return one row containing COUNT(*) and COALESCE(SUM(points), 0) for learners with points > 100.
Common trap
COUNT(city) is not a reliable count of learners when city can be NULL.
Further reading: SQLite SELECT reference
Next lesson: Build a grouped report and filter groups →