SQL GROUP BY, HAVING and Aggregate Functions Explained with Examples

Run the next query on a table of library loans and the database answers COUNT(*) = 12 next to COUNT(returned_on) = 9. Both are counts of the same table, in the same query, and they disagree by three. Understanding that gap is most of what you need to know about aggregates, and it is a better first lesson than the syntax.

Collapsing many rows into one

An aggregate function reads a whole set of rows and returns a single value. The table below holds twelve loans from three branches of a small library network. A NULL in returned_on means the book is still out.

CREATE TABLE loans (
  loan_id     INTEGER PRIMARY KEY,
  branch      TEXT    NOT NULL,
  genre       TEXT    NOT NULL,
  title       TEXT    NOT NULL,
  loaned_on   TEXT    NOT NULL,
  returned_on TEXT,                       -- NULL = still on loan
  fee_cents   INTEGER NOT NULL DEFAULT 0  -- late fee charged
);

INSERT INTO loans (branch, genre, title, loaned_on, returned_on, fee_cents) VALUES
  ('Harbour', 'mystery', 'The Lantern Thief',     '2025-03-02', '2025-03-20', 0),
  ('Harbour', 'mystery', 'Salt and Ashes',        '2025-03-05', '2025-04-02', 140),
  ('Harbour', 'science', 'Orbits for Beginners',  '2025-03-06', '2025-03-19', 0),
  ('Harbour', 'poetry',  'Tide Tables',           '2025-03-09', NULL,         0),
  ('Uptown',  'mystery', 'The Lantern Thief',     '2025-03-03', '2025-03-30', 90),
  ('Uptown',  'science', 'Cells and Systems',     '2025-03-04', '2025-03-18', 0),
  ('Uptown',  'science', 'Orbits for Beginners',  '2025-03-10', NULL,         0),
  ('Uptown',  'science', 'A Short Book of Atoms', '2025-03-12', '2025-03-29', 60),
  ('Uptown',  'poetry',  'Tide Tables',           '2025-03-15', '2025-03-28', 0),
  ('Mill Rd', 'mystery', 'Salt and Ashes',        '2025-03-07', '2025-03-21', 0),
  ('Mill Rd', 'poetry',  'Evening Fields',        '2025-03-08', '2025-03-22', 0),
  ('Mill Rd', 'poetry',  'Tide Tables',           '2025-03-13', NULL,         0);

SELECT * FROM loans;

loan_id | branch  | genre   | title                 | loaned_on  | returned_on | fee_cents
--------+---------+---------+-----------------------+------------+-------------+----------
      1 | Harbour | mystery | The Lantern Thief     | 2025-03-02 | 2025-03-20  |         0
      2 | Harbour | mystery | Salt and Ashes        | 2025-03-05 | 2025-04-02  |       140
      3 | Harbour | science | Orbits for Beginners  | 2025-03-06 | 2025-03-19  |         0
      4 | Harbour | poetry  | Tide Tables           | 2025-03-09 | NULL        |         0
      5 | Uptown  | mystery | The Lantern Thief     | 2025-03-03 | 2025-03-30  |        90
      6 | Uptown  | science | Cells and Systems     | 2025-03-04 | 2025-03-18  |         0
      7 | Uptown  | science | Orbits for Beginners  | 2025-03-10 | NULL        |         0
      8 | Uptown  | science | A Short Book of Atoms | 2025-03-12 | 2025-03-29  |        60
      9 | Uptown  | poetry  | Tide Tables           | 2025-03-15 | 2025-03-28  |         0
     10 | Mill Rd | mystery | Salt and Ashes        | 2025-03-07 | 2025-03-21  |         0
     11 | Mill Rd | poetry  | Evening Fields        | 2025-03-08 | 2025-03-22  |         0
     12 | Mill Rd | poetry  | Tide Tables           | 2025-03-13 | NULL        |         0

With no GROUP BY, the whole table is one group, so a single query can produce one summary row.

SELECT COUNT(*)           AS loans,
       COUNT(returned_on)  AS returned,
       SUM(fee_cents)      AS fees,
       ROUND(AVG(fee_cents), 1) AS avg_fee,
       MIN(loaned_on)      AS first_loan,
       MAX(loaned_on)      AS last_loan
FROM loans;

loans | returned | fees | avg_fee | first_loan | last_loan
------+----------+------+---------+------------+-----------
   12 |        9 |  290 |    24.2 | 2025-03-02 | 2025-03-15

COUNT(*) counts rows. COUNT(returned_on) counts rows where that column is not NULL, which is why it is 9: three loans are still out. SUM, AVG, MIN and MAX also skip NULLs. That behaviour is usually what you want, but it deserves a conscious thought each time, because an average computed over fewer rows than you assumed is a quiet error. (The fee column is declared NOT NULL DEFAULT 0, so its average here covers all twelve loans.)

GROUP BY: one summary row per group

GROUP BY splits the rows into buckets by the listed columns and applies the aggregates to each bucket separately. Group by two columns and every distinct pair becomes a bucket.

SELECT branch, COUNT(*) AS loans, SUM(fee_cents) AS fees
FROM loans
GROUP BY branch
ORDER BY loans DESC;

SELECT branch, genre, COUNT(*) AS loans
FROM loans
GROUP BY branch, genre
ORDER BY branch, loans DESC, genre;

branch  | loans | fees
--------+-------+-----
Uptown  |     5 |  150
Harbour |     4 |  140
Mill Rd |     3 |    0

branch  | genre   | loans
--------+---------+------
Harbour | mystery |     2
Harbour | poetry  |     1
Harbour | science |     1
Mill Rd | poetry  |     2
Mill Rd | mystery |     1
Uptown  | science |     3
Uptown  | mystery |     1
Uptown  | poetry  |     1

The first result answers "which branch lends and charges the most": Uptown, with five loans and 150 cents of fees. The second breaks the same data down by branch and genre. Ties inside a branch are ordered by the extra genre column so that the output is repeatable.

WHERE filters rows, HAVING filters groups

Suppose you want only the branches whose fees exceed 100. A natural first attempt puts the aggregate in WHERE, and the database refuses.

SELECT branch FROM loans WHERE SUM(fee_cents) > 100 GROUP BY branch;

Error: misuse of aggregate: SUM()

The reason is the processing order from the previous lesson: WHERE runs before any groups exist, so a group total is not yet known. HAVING runs after grouping and may use aggregates. Use WHERE to throw away individual rows early (cheaper), and HAVING only for conditions on the aggregates.

SELECT branch, COUNT(*) AS loans, SUM(fee_cents) AS fees
FROM loans
GROUP BY branch
HAVING SUM(fee_cents) > 100;

SELECT genre,
       COUNT(*)              AS loans,
       COUNT(DISTINCT title) AS distinct_titles
FROM loans
WHERE returned_on IS NOT NULL
GROUP BY genre
HAVING COUNT(*) >= 3
ORDER BY genre;

branch  | loans | fees
--------+-------+-----
Harbour |     4 |  140
Uptown  |     5 |  150

genre   | loans | distinct_titles
--------+-------+----------------
mystery |     4 |               2
science |     3 |               3

The first query keeps Harbour and Uptown because their fee totals exceed 100; Mill Rd is filtered out after grouping. The second combines both filters: WHERE first removes loans still out, then HAVING keeps genres with at least three returned loans. poetry drops out because only two of its four loans have come back, and COUNT(DISTINCT title) shows that the science loans cover three different titles.

The NULL and empty-group corners

Two quirks cause real reporting errors. First, AVG ignores NULL but a zero is a real value, so the average fee per loan and the average fee among loans that were charged are different numbers. The trick of wrapping a CASE expression inside an aggregate lets you compute both in one pass; CASE returns NULL when no branch matches, and the aggregate skips it. Second, SUM over zero rows returns NULL, not 0, while COUNT returns 0.

SELECT genre,
       ROUND(AVG(fee_cents), 1) AS avg_all_loans,
       AVG(CASE WHEN fee_cents > 0 THEN fee_cents END) AS avg_when_charged
FROM loans
GROUP BY genre
ORDER BY genre;

SELECT COUNT(*) AS n, SUM(fee_cents) AS sum_fees, TOTAL(fee_cents) AS total_fees
FROM loans
WHERE branch = 'Nowhere';

genre   | avg_all_loans | avg_when_charged
--------+---------------+-----------------
mystery |          57.5 |            115.0
poetry  |           0.0 |             NULL
science |          15.0 |             60.0

n | sum_fees | total_fees
--+----------+-----------
0 | NULL     |        0.0

In the first table, poetry loans have an average of 0.0 across all rows, but nothing was ever charged, so "average when charged" is NULL rather than a misleading 0. In the second, a branch that does not exist yields COUNT(*) = 0 and SUM of NULL. TOTAL is an SQLite function that returns 0.0 for empty sets; in other databases you would write COALESCE(SUM(fee_cents), 0).

Common mistakes

• Selecting a column that is neither grouped nor aggregated. In PostgreSQL this fails with column "loans.title" must appear in the GROUP BY clause or be used in an aggregate function. SQLite and older MySQL modes accept it and return an arbitrary row's value, which is worse, because the result looks plausible. The query below shows it.
• Aggregate in WHERE. Error: misuse of aggregate: SUM() in recent SQLite versions (older ones say misuse of aggregate function SUM()). Move the condition to HAVING.
• **Assuming COUNT(col) equals COUNT(*).** Symptom: totals that are lower than the number of rows. Count the column only when you mean non-NULL values.
• Averaging averages. The mean of per-branch averages is not the overall average when branches have different numbers of loans. Compute from the raw rows.
• Forgetting to alias aggregates. Without AS loans, the column is named COUNT(*), which breaks code that reads results by name.

SELECT branch, title, COUNT(*) AS loans
FROM loans
GROUP BY branch;

branch  | title             | loans
--------+-------------------+------
Harbour | The Lantern Thief |     4
Mill Rd | Salt and Ashes    |     3
Uptown  | The Lantern Thief |     5

Harbour shows The Lantern Thief even though the branch lent four different books. SQLite picked one row's title with no rule you could rely on. Treat this as a bug in your query, not a feature.

How this shows up in real projects and interviews

Dashboards, monthly reports and data checks are mostly aggregates: orders per day, errors per service, loans per branch. Interview screens ask for "the top N groups with more than K rows" or ask why COUNT(*) and COUNT(col) differ, and they probe whether you know the WHERE-versus-HAVING split. Once rows live in several tables, the same summaries need joins, which is where the next lesson, SQL JOINs, picks up.