WHERE and HAVING answer different questions

A teacher is preparing an after-school programme. She wants to find subjects offering at least 90 minutes of online teaching. Robotics has two online lessons: one lasts 40 minutes and the other lasts 50. Together they qualify, even though neither lesson lasts 90 minutes.

That distinction tells us where two SQL conditions belong. One decides which lessons to include. The other decides whether their combined time is enough. We will build a small SQLite table, write the query and then see how two plausible changes answer different questions.

You should recognize SELECT, FROM, WHERE and a basic SUM. The lessons and numbers are invented, so you can check every result by hand. To run the SQL, use a fresh SQLite database. The optional Python example uses the standard-library sqlite3 module and creates its database in memory.

Say which lessons should contribute

Our request contains two rules:

• Include lessons delivered online.
• Keep subjects whose included lessons add up to at least 90 minutes.

The first can be checked by looking at one lesson. The second needs all the included lessons for a subject. Underline those two parts of the question before typing SQL.

Here is the complete dataset. Each row represents one lesson. In this example, minutes contains positive whole numbers, and delivery identifies how the lesson is taught.

CREATE TABLE lessons (
    lesson_id INTEGER PRIMARY KEY,
    subject TEXT NOT NULL,
    delivery TEXT NOT NULL,
    minutes INTEGER NOT NULL CHECK (minutes > 0)
);

INSERT INTO lessons VALUES
    (1, 'Robotics', 'online', 40),
    (2, 'Robotics', 'online', 50),
    (3, 'Robotics', 'classroom', 90),
    (4, 'Art', 'online', 30),
    (5, 'Art', 'online', 35),
    (6, 'Art', 'classroom', 120),
    (7, 'Music', 'online', 60),
    (8, 'Music', 'classroom', 80);

Robotics contributes 40 + 50 = 90 online minutes. Art contributes 30 + 35 = 65. Music contributes 60. The classroom lessons should not enter those totals. Keep those three results beside the query as a check.

Put each condition where it belongs

SELECT subject, SUM(minutes) AS online_minutes
FROM lessons
WHERE delivery = 'online'
GROUP BY subject
HAVING SUM(minutes) >= 90
ORDER BY subject;

The query returns one row:

Read the query as a sequence of questions. WHERE keeps the online input rows. GROUP BY subject gathers those rows into subject groups. SUM(minutes) adds the minutes within each group; a calculation across rows in a group is called an aggregate. HAVING keeps the groups whose sum reaches 90. Finally, ORDER BY sorts the output by subject. SQLite's SELECT explanation, SUM documentation

For these rows, cross out lesson IDs 3, 6 and 8 because they are classroom lessons. Five rows remain. Write each under its subject heading, add the minutes and keep headings whose total reaches 90. This produces Robotics, just as the query does.

The filters apply to different things: lessons first, subject totals second. This is a logical teaching model; the database engine may choose a different physical execution plan.

The name online_minutes is an output label we chose using AS. It tells readers which minutes the number includes, which will matter when we compare it with classroom time.

Watch Robotics disappear under a different rule

Suppose we move the 90-minute condition into WHERE:

SELECT subject, SUM(minutes) AS online_minutes
FROM lessons
WHERE delivery = 'online' AND minutes >= 90
GROUP BY subject
ORDER BY subject;

This query returns no rows. Every online lesson is shorter than 90 minutes. Robotics loses both its 40-minute and 50-minute lessons before their minutes can be added.

The SQL is valid, but it answers a different question: “For each subject, add the online lessons that individually last at least 90 minutes.” That is not the programme's original requirement. Valid syntax cannot decide which question we meant to ask.

Now take the first query and remove its WHERE line instead. All three subjects qualify: Art has 185 minutes, Music has 140 and Robotics has 180. Those totals include classroom teaching. Check Art by hand: 30 + 35 + 120 = 185. The arithmetic is correct for the changed question, but the report no longer describes online time.

These variations reveal two different mistakes. One removes short lessons that should contribute. The other includes classroom lessons that should not contribute. In a large dataset, either could produce a convincing-looking report.

Check the group rather than an arbitrary lesson

An aggregate condition such as SUM(minutes) >= 90 belongs in HAVING in our grouped query. Putting that aggregate in WHERE produces a SQLite error; the companion script checks that rejection.

HAVING can also contain non-aggregate expressions, including a condition on a grouping key. So “WHERE is for rows, HAVING is for groups” is a starting point for reading this query, rather than a complete rule for every possible expression. SQLite's result generation

Our output selects subject, the grouping key, alongside its sum. Adding a lesson ID to that output would create another question: which individual lesson should a subject total represent? SQLite permits some ungrouped output columns, but they can refer to an arbitrarily chosen row. Leave them out when they do not describe the result you need. SQLite's bare-column behavior

Your turn

Using the same eight lessons, find subjects with at least 100 minutes of classroom teaching. Return each subject and its classroom minutes, ordered by subject. Predict the answer before changing the query. Which word changes the row filter, and which number changes the group filter?

SELECT subject, SUM(minutes) AS classroom_minutes
FROM lessons
WHERE delivery = 'classroom'
GROUP BY subject
HAVING SUM(minutes) >= 100
ORDER BY subject;

What to remember

The complete Python example (the complete code is below) creates the eight lessons in an in-memory SQLite database, checks these results and closes the connection. It uses execute() to run a query and fetchall() to collect its result rows. Python's SQLite interface

When a grouped query surprises you, write down what one input row means, which rows should contribute and what makes a finished group qualify. Then check one group manually. For Robotics, 40 + 50 = 90 explains the whole result: the individual lessons are short enough to fail the wrong row condition, while their total meets the intended group condition.

"""Synthetic classroom example: row filtering versus group filtering.
Run with Python 3. No packages, network calls, or persistent database required.
"""
import sqlite3

SETUP_SQL = """
CREATE TABLE lessons (
    lesson_id INTEGER PRIMARY KEY,
    subject TEXT NOT NULL,
    delivery TEXT NOT NULL,
    minutes INTEGER NOT NULL CHECK (minutes > 0)
);
INSERT INTO lessons VALUES
    (1, 'Robotics', 'online', 40),
    (2, 'Robotics', 'online', 50),
    (3, 'Robotics', 'classroom', 90),
    (4, 'Art', 'online', 30),
    (5, 'Art', 'online', 35),
    (6, 'Art', 'classroom', 120),
    (7, 'Music', 'online', 60),
    (8, 'Music', 'classroom', 80);
"""

MAIN_SQL = """
SELECT subject, SUM(minutes) AS online_minutes
FROM lessons
WHERE delivery = 'online'
GROUP BY subject
HAVING SUM(minutes) >= 90
ORDER BY subject;
"""


def main():
    con = sqlite3.connect(':memory:')
    try:
        con.executescript(SETUP_SQL)
        row_ids = con.execute("""
            SELECT lesson_id FROM lessons
            WHERE delivery = 'online' ORDER BY lesson_id;
        """).fetchall()
        assert row_ids == [(1,), (2,), (4,), (5,), (7,)]

        online_totals = con.execute("""
            SELECT subject, SUM(minutes)
            FROM lessons WHERE delivery = 'online'
            GROUP BY subject ORDER BY subject;
        """).fetchall()
        assert online_totals == [('Art', 65), ('Music', 60), ('Robotics', 90)]

        result = con.execute(MAIN_SQL).fetchall()
        assert result == [('Robotics', 90)]
        print('Online totals:', online_totals)
        print('Online groups at least 90 minutes:', result)

        all_formats = con.execute("""
            SELECT subject, SUM(minutes)
            FROM lessons GROUP BY subject
            HAVING SUM(minutes) >= 90 ORDER BY subject;
        """).fetchall()
        assert all_formats == [('Art', 185), ('Music', 140), ('Robotics', 180)]
        print('All-format groups at least 90 minutes:', all_formats)

        long_individual_lessons = con.execute("""
            SELECT subject, SUM(minutes)
            FROM lessons
            WHERE delivery = 'online' AND minutes >= 90
            GROUP BY subject ORDER BY subject;
        """).fetchall()
        assert long_individual_lessons == []
        print('Online lessons individually at least 90 minutes:', long_individual_lessons)

        try:
            con.execute("""
                SELECT subject, SUM(minutes)
                FROM lessons
                WHERE delivery = 'online' AND SUM(minutes) >= 90
                GROUP BY subject;
            """).fetchall()
        except sqlite3.OperationalError as exc:
            assert 'aggregate' in str(exc).lower()
        else:
            raise AssertionError('Expected SQLite to reject aggregate in WHERE')

        exercise = con.execute("""
            SELECT subject, SUM(minutes) AS classroom_minutes
            FROM lessons
            WHERE delivery = 'classroom'
            GROUP BY subject
            HAVING SUM(minutes) >= 100
            ORDER BY subject;
        """).fetchall()
        assert exercise == [('Art', 120)]
        assert con.execute(MAIN_SQL.replace('>= 90', '> 90')).fetchall() == []
        print('Exercise answer:', exercise)
        print('PASS: 8 checks; SQLite', sqlite3.sqlite_version)
    finally:
        con.close()


if __name__ == '__main__':
    main()