Querying with SELECT, GROUP BY, and HAVING

Filter rows, group them, filter the groups, and sort the results.

Python

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE sale (region TEXT, product TEXT, amount INTEGER)")
db.executemany(
    "INSERT INTO sale VALUES (?, ?, ?)",
    [
        ("north", "pen", 10),
        ("north", "ink", 25),
        ("south", "pen", 5),
        ("south", "ink", 40),
        ("south", "pad", 15),
        ("east", "pen", 8),
    ],
)
rows = db.execute(
    "SELECT region, COUNT(*) AS orders, SUM(amount) AS total "
    "FROM sale WHERE amount >= 8 "
    "GROUP BY region HAVING SUM(amount) > 20 "
    "ORDER BY total DESC"
).fetchall()
for row in rows:
    print(row)

Output

('south', 2, 55)
('north', 2, 35)

Explanation

SQL is declarative: you describe the result you want and the database decides how to compute it. A SELECT statement is written in the order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, but it is evaluated logically in a different order: FROM, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and finally LIMIT.

That order explains the difference between WHERE and HAVING. WHERE filters individual rows before they are grouped, so it cannot use aggregate results. HAVING filters whole groups after aggregates such as COUNT and SUM have been computed. In the example, rows under 8 are dropped first, then each region's rows are grouped, then only regions whose total exceeds 20 are kept, then the survivors are sorted by total.

Always pass values to a query with placeholders (the ? marks) and never build SQL by joining strings. The query below has no user input, but the sale rows are inserted with placeholders as the habit to keep.

Explore the dry run

Guided walkthroughReady

1import sqlite3

3db = sqlite3.connect(":memory:")

4db.execute("CREATE TABLE sale (region TEXT, product TEXT, amount INTEGER)")

5db.executemany(

6 "INSERT INTO sale VALUES (?, ?, ?)",

7 [

8 ("north", "pen", 10),

9 ("north", "ink", 25),

10 ("south", "pen", 5),

11 ("south", "ink", 40),

12 ("south", "pad", 15),

13 ("east", "pen", 8),

14 ],

15)

16rows = db.execute(

17 "SELECT region, COUNT(*) AS orders, SUM(amount) AS total "

18 "FROM sale WHERE amount >= 8 "

19 "GROUP BY region HAVING SUM(amount) > 20 "

20 "ORDER BY total DESC"

21).fetchall()

22for row in rows:

23 print(row)

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Six sale rows are inserted with placeholders, not string concatenation.	`—`	—
WHERE amount >= 8 keeps 5 rows: the south pen row of 5 is removed.	`after WHERE: north 10, 25; south 40, 15; east 8`	—
GROUP BY makes three groups. HAVING keeps north (35) and south (55) and drops east (8). ORDER BY total DESC puts south first.	`groups kept: south, north`	('south', 2, 55) ('north', 2, 35)

Cost and common mistakes

Without indexes the query scans the n rows once and groups them with a hash or sort, roughly O(n) to O(n log n) time.

WHERE cannot reference aggregates such as SUM, because it runs before grouping. Use HAVING for conditions on groups.
Without ORDER BY, SQL does not guarantee any row order, even if results usually look sorted.
Selecting a column that is neither grouped nor aggregated is an error in most databases and gives an arbitrary value in SQLite.

Try it yourself

Change WHERE amount >= 8 to WHERE amount >= 20. Which rows come back?

Reveal answer and reasoning

Only the rows with amount 25, 40 survive, giving ('south', 1, 40) and ('north', 1, 25) after HAVING SUM(amount) > 20.

Querying with SELECT, GROUP BY, and HAVING

Explanation

Follow the example

Cost and common mistakes

Try it yourself

Further reading