Important topics: Aggregation and grouping

1.1 Aggregates turn many rows into one

An aggregate function reads a set of rows and returns a single value. COUNT counts, SUM adds, AVG averages, and MIN and MAX pick the smallest and largest value. Without a GROUP BY, the whole table is one set and the query returns exactly one row, even when the table is empty. With a GROUP BY, the engine splits the rows into groups and computes one value per group.

Aggregates ignore NULL in their own ways, and that asymmetry is where most confusion starts. COUNT(*) counts rows. COUNT(column) counts only the rows where that column is not NULL. SUM, AVG, MIN and MAX skip NULL inputs entirely, so an average is computed over the known values only, not over every row. The result of SUM over zero rows is NULL, while COUNT over zero rows is 0.

Aggregates also accept an optional DISTINCT: COUNT(DISTINCT city) counts distinct non-NULL cities, and AVG(DISTINCT rating) averages each distinct rating once. Use it when the question asks for variety rather than volume, and remember that it cannot be combined with *. Every example in this module runs on SQLite 3.53.

SELECT COUNT(*) AS all_rows,
       COUNT(rating) AS rated_rows,
       COUNT(DISTINCT status) AS statuses,
       COUNT(DISTINCT rating) AS distinct_ratings
FROM enrollments;

all_rows | rated_rows | statuses | distinct_ratings
---------+------------+----------+-----------------
14       | 9          | 3        | 4

1.2 The five built-in aggregates

SUM adds numeric values and returns NULL for an empty input. AVG divides the sum by the number of non-NULL values, so one missing value does not pull the denominator down; the average of 4, 5 and NULL is 4.5, not 3. MIN and MAX work on text as well as numbers and use the default ordering of their storage class, which is lexicographic for text in binary collation. This is why MIN(name) returns the alphabetically first name, not the shortest one.

AVG returns a floating point number in SQLite even when the inputs are integers. SUM keeps an integer result until the total exceeds the integer range, and then it switches to REAL. Wrapping the result in ROUND(value, 2) is a display choice, not a storage change. In a report, state whether the average includes unknown values as zero or excludes them, because the two answers differ.

SELECT COUNT(*) AS courses,
       SUM(fee) AS total_fee,
       AVG(fee) AS avg_fee,
       MIN(fee) AS cheapest,
       MAX(fee) AS costliest
FROM courses;

courses | total_fee | avg_fee           | cheapest | costliest
--------+-----------+-------------------+----------+----------
6       | 33994     | 5665.666666666667 | 3999     | 8999

1.3 GROUP BY builds the buckets

GROUP BY names one or more expressions and puts every row into a bucket that shares those values. The select list may then contain the grouping expressions and aggregate calls over each bucket, which produces one output row per group. The rows inside a group have no order, and the groups themselves come back in no guaranteed order unless ORDER BY says otherwise.

The rule to remember is that every non-aggregated expression in the select list should also appear in GROUP BY. SQLite is permissive and will pick an arbitrary row's value when you break the rule, while PostgreSQL rejects the query and MySQL rejects it under ONLY_FULL_GROUP_BY, which is on by default since MySQL 5.7. A permissive engine does not make the query meaningful; the value you get back is simply not defined by the statement.

Group counts are often more useful than raw row counts. The query below shows active enrollments first, then completed, then dropped, and the total of the group counts is the row count of the table.

SELECT status, COUNT(*) AS enrollments
FROM enrollments
GROUP BY status
ORDER BY enrollments DESC;

status    | enrollments
----------+------------
active    | 10
completed | 3
dropped   | 1

1.4 HAVING filters groups

WHERE filters rows before grouping and HAVING filters groups after grouping. That order explains what each clause can see. WHERE cannot use an aggregate, because no group exists yet, and HAVING can use aggregates and the grouping columns because groups already exist. A query can use both: filter the raw rows with WHERE, group the survivors, then drop small groups with HAVING.

Prefer WHERE for conditions on individual rows because it reduces the amount of work before grouping. Use HAVING only for conditions that need the group's aggregate. Writing HAVING status = 'active' works, but WHERE status = 'active' is clearer and cheaper. SQLite accepts an aggregate alias inside HAVING as well, though repeating the aggregate expression is the portable form.

SELECT course_code, COUNT(*) AS active_count
FROM enrollments
WHERE status = 'active'
GROUP BY course_code
HAVING COUNT(*) >= 2
ORDER BY active_count DESC, course_code;

course_code | active_count
------------+-------------
DS          | 3
SQL         | 3
PY          | 2

1.5 Grouping by expressions and by several columns

The grouping expression does not have to be a stored column. strftime('%Y-%m', enrolled_on) groups by calendar month, which is a common report shape. SQLite and PostgreSQL also accept a select alias in GROUP BY as an extension, even though standard SQL resolves GROUP BY before the select list, so spelling the expression out is the portable choice.

Several columns produce a bucket for each combination, not a nested list. GROUP BY course_code, status returns one row per course and status pair, and a pair that never occurs simply gets no row. That is the difference between seeing "PY has two active, one completed and one dropped enrollment" and seeing only "PY has four enrollments".

SELECT strftime('%Y-%m', enrolled_on) AS month, COUNT(*) AS enrollments
FROM enrollments
GROUP BY month
ORDER BY month;

month   | enrollments
--------+------------
2026-01 | 3
2026-02 | 5
2026-03 | 6

SELECT course_code, status, COUNT(*) AS n
FROM enrollments
GROUP BY course_code, status
ORDER BY course_code, status;

course_code | status    | n
------------+-----------+--
DS          | active    | 3
ML          | active    | 1
PY          | active    | 2
PY          | completed | 1
PY          | dropped   | 1
SQL         | active    | 3
SQL         | completed | 2
WEB         | active    | 1

1.6 NULLs and empty input

NULL is not equal to NULL for comparisons, but GROUP BY treats NULLs as one key, so every row with an unknown value lands in the same bucket. That bucket is real and deserves a decision: label it as unknown in the report, or exclude it with WHERE rating IS NOT NULL if it is out of scope. The aggregate functions then skip NULL inputs inside the other buckets.

A NULL group can be filtered like any other group, since HAVING runs on the group key after the buckets exist. HAVING rating IS NULL keeps only the unknown bucket, and WHERE rating IS NULL keeps the same rows earlier and more cheaply. Use the WHERE form when the question is about rows, and the HAVING form only when the query needs to count the unknown bucket alongside the known ones.

Empty input has two different outcomes. A grouped query over zero rows returns zero rows, because there are no groups. A global aggregate without GROUP BY returns exactly one row: COUNT(*) gives 0, and SUM and AVG give NULL. That single row with a NULL total is a common surprise in dashboards, and COALESCE(SUM(amount), 0) is the usual fix when a numeric zero is needed.

SELECT rating, COUNT(*) AS n
FROM enrollments
GROUP BY rating
ORDER BY rating;

rating | n
-------+--
NULL   | 5
2      | 1
3      | 2
4      | 3
5      | 3

SELECT COUNT(*) AS rows_found,
       SUM(rating) AS rating_sum,
       AVG(rating) AS rating_avg
FROM enrollments
WHERE course_code = 'CN';

rows_found | rating_sum | rating_avg
-----------+------------+-----------
0          | NULL       | NULL

-- error demo: an aggregate cannot appear in WHERE
SELECT course_code
FROM enrollments
WHERE COUNT(*) > 1
GROUP BY course_code;

ERROR: misuse of aggregate: COUNT()

1.7 Common mistakes

• Asking COUNT(column) for a row count and getting a smaller number because of NULLs.
• Using HAVING for a condition on individual rows instead of WHERE.
• Selecting a bare column that is not in GROUP BY, then reading its arbitrary value as fact.
• Comparing SUM of an empty set with zero. It is NULL unless you wrap it.
• Forgetting that an aggregate in WHERE is an error, not a filter on groups.