Skip to content
BytePatterns

SQL GROUP BY and HAVING Explained: WHERE vs HAVING

8 min readBytePatterns

How GROUP BY folds rows into one row per group, why aggregates belong in HAVING and not WHERE, and what COUNT, SUM and NULL really do. Each query run in SQLite.

"Total sales per region, but only regions over a million." That one sentence needs three ideas: fold many rows into one number, do it once per group, and filter the groups afterwards. SQL spells them SUM, GROUP BY and HAVING, and most mistakes come from putting a filter on the wrong side of the grouping. Every query below was run with Python's sqlite3 module against SQLite 3.37; the rules quoted come from the SQLite and PostgreSQL documentation listed at the end, as of September 2026.

The problem it solves

A table stores facts one row at a time: one weighing per picker per day, one order per customer, one request per log line. Reports want the opposite shape, one row per picker, per customer, per endpoint, carrying a total, a count or an average.

An aggregate function computes a single result from many input rows: COUNT, SUM, AVG, MIN, MAX. On its own it collapses the whole table into one row. GROUP BY decides how many piles there are: one per distinct value of the grouping columns, and the aggregate runs once per pile.

The intuition

Read a grouped query in the order the database logically applies it, not the order it is written:

  • FROM picks the rows.
  • WHERE throws away individual rows. Aggregates do not exist yet.
  • GROUP BY sorts the survivors into piles.
  • Aggregates are computed per pile.
  • HAVING throws away whole piles, and it can test aggregates, because they now exist.
  • SELECT produces one output row per remaining pile, and ORDER BY sorts them.

The PostgreSQL tutorial states the difference directly: WHERE selects input rows before groups and aggregates are computed, and HAVING selects group rows after. That is why WHERE SUM(kilos) > 20 is an error rather than a slow query: the sum it asks about has not been computed at the point where WHERE runs.

A rule of thumb follows. A condition on a row belongs in WHERE; a condition on a group belongs in HAVING. A row condition put in HAVING often still works, but it makes the database group and aggregate rows it could have discarded first.

Watch it run

The animation uses the lesson's tea-estate table, five weighings by three pickers. GROUP BY picker makes three piles. Meena's two lines fold into SUM = 40.0 and COUNT(*) = 2, Ravi's into 17.5, and Suri's single line is her whole group. Five rows become three. The last frame applies HAVING SUM(kilos) >= 20, which tests whole groups, and Ravi's pile is removed.

Aggregations & GROUP BY

Step 1 of 10

Five weighings at the shed, one line per picker per day. Payroll never reads them singly.

The same interactive animation as the lesson — step through it with the controls.

The code

The lesson's table with two additions: a missing weighing for Suri, stored as NULL, and a basket with no picker recorded. COUNT(*) counts rows; COUNT(kilos) counts non-null values:

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE pluck (picker TEXT, day TEXT, kilos REAL);
  INSERT INTO pluck VALUES ('Meena','Mon',18.0), ('Meena','Tue',22.0),
                           ('Ravi','Mon',9.5),   ('Ravi','Tue',8.0),
                           ('Suri','Mon',30.0),  ('Suri','Tue',NULL),
                           (NULL,'Tue',5.0);
""")
q = lambda sql: db.execute(sql).fetchall()

print(q("""SELECT picker, SUM(kilos), COUNT(*), COUNT(kilos)
           FROM pluck GROUP BY picker ORDER BY picker"""))
# [(None, 5.0, 1, 1), ('Meena', 40.0, 2, 2), ('Ravi', 17.5, 2, 2), ('Suri', 30.0, 2, 1)]

Suri has two rows but one weighing, so COUNT(*) and COUNT(kilos) disagree. The unassigned basket forms its own group: for grouping, NULL values are considered equal.

WHERE and HAVING in one query. Only Monday's rows are grouped, then only groups with at least 10 kilos survive:

print(q("""SELECT picker, SUM(kilos) FROM pluck
           WHERE day = 'Mon'
           GROUP BY picker HAVING SUM(kilos) >= 10 ORDER BY picker"""))
# [('Meena', 18.0), ('Suri', 30.0)]

try:
    q("SELECT picker FROM pluck WHERE SUM(kilos) > 20")
except sqlite3.OperationalError as e:
    print(e)                      # misuse of aggregate function SUM()

Two behaviours that surprise people. SUM over no rows is NULL, not zero, as the SQL standard requires; SQLite's TOTAL returns 0.0 instead. And SQLite accepts a bare column, one neither grouped nor aggregated, where most engines raise an error:

print(q("SELECT SUM(kilos), TOTAL(kilos), COUNT(*) FROM pluck WHERE picker = 'Nobody'"))
# [(None, 0.0, 0)]

print(q("SELECT picker, day, MAX(kilos) FROM pluck GROUP BY picker ORDER BY picker"))
# [(None, 'Tue', 5.0), ('Meena', 'Tue', 22.0), ('Ravi', 'Mon', 9.5), ('Suri', 'Mon', 30.0)]

With exactly one MIN or MAX in the query, SQLite documents that bare columns come from the row holding that minimum or maximum, which is why day is right here. With any other aggregate, the row is arbitrary. PostgreSQL rejects the query unless day is functionally dependent on the grouped columns.

Finally, the grouped query against a hand-written reference that applies the same steps in Python, on 300 random tables full of NULL pickers and NULL weights:

import random
from collections import defaultdict

def reference(rows, min_total):
    groups = defaultdict(list)
    for picker, day, kilos in rows:
        if day == "Mon":                                   # WHERE: rows first
            groups[picker].append(kilos)
    out = []
    for picker, ks in groups.items():
        vals = [k for k in ks if k is not None]            # SUM skips NULLs
        total = sum(vals) if vals else None
        if total is not None and total >= min_total:       # HAVING: groups after
            out.append((picker, total, len(ks), len(vals)))
    return sorted(out, key=lambda r: (r[0] is not None, r[0] or ""))

random.seed(4)
ok = True
for _ in range(300):
    rows = [(random.choice(["A", "B", "C", None]), random.choice(["Mon", "Tue"]),
             random.choice([None, 1.0, 2.5, 4.0, 7.0])) for _ in range(random.randint(0, 15))]
    t = sqlite3.connect(":memory:")
    t.execute("CREATE TABLE pluck (picker TEXT, day TEXT, kilos REAL)")
    t.executemany("INSERT INTO pluck VALUES (?, ?, ?)", rows)
    min_total = random.choice([0, 3, 6])
    got = t.execute("""SELECT picker, SUM(kilos), COUNT(*), COUNT(kilos) FROM pluck
                       WHERE day = 'Mon' GROUP BY picker
                       HAVING SUM(kilos) >= ? ORDER BY picker""", (min_total,)).fetchall()
    ok &= got == reference(rows, min_total)
print(ok)                                                 # True

A group whose weights are all NULL has a NULL sum, and NULL >= 0 is not true, so HAVING drops it even with a threshold of zero. The reference has to say that explicitly, and the random tables exercise it.

The complexity

The database chooses the algorithm, but the costs are easy to reason about:

  • Hash aggregation keeps one running total per group in a hash table: one pass over the rows, memory proportional to the number of groups.
  • Sort-based aggregation sorts by the grouping columns, then folds each run of equal keys: O(n log n) for the sort, unless an index already delivers the rows in that order.
  • WHERE shrinks the input before either happens, which is why a row filter belongs there and not in HAVING.

Where it goes wrong

  • An aggregate in WHERE. It is an error; move the condition to HAVING.
  • COUNT(column) when rows were meant. It skips NULL values. Use COUNT(*) to count rows.
  • Expecting SUM of nothing to be zero. It is NULL. Wrap it in COALESCE(SUM(x), 0) when a report needs a number.
  • Bare columns. SQLite accepts them and picks a row; PostgreSQL rejects them. Portable SQL groups by, or aggregates, every selected column.
  • Aliases in HAVING. PostgreSQL allows an output column's name in ORDER BY and GROUP BY, but not in WHERE or HAVING, where the expression must be written out.

How to say it in an interview

"GROUP BY makes one group per distinct value of the grouping columns, and aggregates like SUM and COUNT are computed per group. WHERE filters rows before grouping, so it can't see aggregates; HAVING filters groups after, so it can. I put row conditions in WHERE so fewer rows get grouped. I'd watch for NULL: COUNT(col) skips it, SUM of no values is NULL, and NULL keys form one group of their own. And every selected column should be grouped or aggregated, because not every engine will pick a row for you."

To keep every row while still seeing a per-group total, the next step is window functions, and the logical clause order used above is covered in query execution order.

Sources