Use HAVING after grouping local records

By Batu · English technical notes

Also published in our primary archive.

Use HAVING after grouping local records

HAVING filters groups after aggregation, whereas WHERE filters individual input rows before grouping. This fixture records minutes against three projects. The query groups the local rows by project, computes SUM(minutes), then keeps only groups whose total reaches the bound threshold of 50. API totals 55 minutes and tests totals 60 minutes, so both appear; docs totals only 25 and is removed.

Using HAVING SUM(minutes) >= ? makes the stage of the condition explicit and keeps the cutoff as a parameter rather than inserting a value into SQL text. The assertion verifies the expected aggregate values for this small data set. ORDER BY project is separately necessary to make the list's order predictable; grouping itself should not be used as an ordering guarantee. If a rule instead concerns a property of each individual entry, put it in WHERE before GROUP BY. For example, filtering out zero-minute entries before summing is a row-level decision, not a group-level threshold.

This uses the Python standard library's sqlite3 module and an in-memory database, with no newer Python-specific API, so Python 3 with sqlite3 is sufficient. The available SQL features still depend on the runtime SQLite library. Python documents execute(), executemany(), placeholders, and fetchall(); SQLite documents the grouping and HAVING portions of SELECT. Python sqlite3 documentation and SQLite SELECT documentation provide the details.

AI assistance disclosure: AI assisted the drafting of this example and explanation.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE entry (project TEXT, minutes INTEGER)")
con.executemany(
    "INSERT INTO entry VALUES (?, ?)",
    [("api", 20), ("api", 35), ("docs", 15),
     ("docs", 10), ("tests", 30), ("tests", 30)],
)

rows = con.execute(
    """
    SELECT project, SUM(minutes) AS total
    FROM entry
    GROUP BY project
    HAVING SUM(minutes) >= ?
    ORDER BY project
    """,
    (50,),
).fetchall()

assert rows == [("api", 55), ("tests", 60)]
con.close()
print(rows)

[('api', 55), ('tests', 60)]