Querying with SELECT, GROUP BY, and HAVING
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.
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)