An aggregate summarises several rows, but a useful summary starts with a clear denominator. Decide whether you are counting records, non-missing values, people or distinct events.
Define the group and the measure
GROUP BY forms groups with shared values; aggregate functions then calculate a result for each group. COUNT(*) counts rows, while COUNT(column) ignores nulls in that column. SUM and AVG have their own handling of missing values. Do not substitute zero for unknown information without deciding whether that changes the meaning of the report.
Separate row filters from group filters
WHERE limits rows before grouping. HAVING filters the resulting groups. A report about groups with at least two submissions needs a group condition, while excluding cancelled submissions is a row-level decision. Write these as separate steps so a reader can see the population behind the statistic.
Check how joins change your totals
Joining one parent to several children can multiply rows before aggregation. Calculate the expected count on a tiny fixture before trusting a dashboard total. If the requirement is distinct learners, a raw joined-row count may be wrong. Name the measure clearly and preserve a way to inspect the underlying records.
Worked example
The fictional submissions table contains two Python records and one SQL record. Grouping by course and requiring at least two rows keeps Python only. The expected total of two is easy to verify by reading the fixture.
CREATE TABLE submissions (course TEXT, learner TEXT);
INSERT INTO submissions VALUES ('Python','Ava'), ('Python','Noah'), ('SQL','Mia');
SELECT course, COUNT(*) AS submissions FROM submissions GROUP BY course HAVING COUNT(*) >= 2 ORDER BY course;
Expected output
Python | 2Check your understanding
Try each question before opening its explanation. These are free practice prompts; they do not use game chances.
Are COUNT(*) and COUNT(score) interchangeable?
Not when score can be NULL. COUNT(*) includes every row in the group, while COUNT(score) counts only rows with a non-null score.
Why does this example use HAVING?
The condition concerns the number of rows in each completed group. WHERE applies to individual rows and does not express this grouped threshold directly.
What happens if a second SQL submission is added?
The SQL group then reaches the threshold of two and appears in the result. This is a useful boundary test for the HAVING condition.
Sources and further reading
These original examples explain foundational concepts. Use the official references for complete, current details and version-specific behaviour.
About this guide: AI-assisted educational content with original examples and linked primary references. Found an issue? Tell us what to improve.