Aggregation: COUNT, SUM and HAVING

Understand the mechanism

GROUP BY forms groups by matching keys, then aggregates compute per group. WHERE filters source rows, while HAVING filters the resulting groups.

COUNT(*) counts rows; COUNT(minutes) counts non-NULL durations. SUM and AVG ignore NULL. An entirely unknown total is not the same as zero.

Select grouping keys or aggregate expressions. SQLite permits some ungrouped bare columns, but they usually do not identify the intended deterministic row. Avoid them for clear, portable queries.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

CREATE TABLE sessions(course TEXT, minutes INTEGER);
INSERT INTO sessions VALUES('Python',25),('Python',35),('SQL',20),('SQL',NULL),('Java',10);
SELECT course, COUNT(*) AS rows_count, COUNT(minutes) AS measured,
       SUM(minutes) AS total
FROM sessions GROUP BY course HAVING SUM(minutes)>=20
ORDER BY total DESC, course;

Trace and verify

Python has 2 rows, 2 measured values and total 60. SQL has 2 rows but 1 measured value and total 20. HAVING excludes Java with total 10.

Common mistakes and boundaries

COUNT(DISTINCT course) counts unique courses, not sessions per course. Define the aggregation grain first.

Practice challenge

Add AVG(minutes) and predict whether SQL averages 10 or 20.

Reference solution and reasoning

It is 20 because NULL is excluded from the denominator. AVG(COALESCE(minutes,0)) would give 10 under an explicit zero-imputation policy, changing the meaning.

Further reading

• SQLite SQL reference