Aggregations & GROUP BY

The Idea

Aggregates like SUM, COUNT and AVG squeeze a pile of rows into one value. GROUP BY decides how many piles there are — one per distinct value. Filter individual rows with WHERE, then filter whole groups with HAVING, which is the only place an aggregate can be tested.

Real-World Example

A tea estate weighs every basket at the shed, one line per picker per day. Payroll never reads those lines individually: it totals kilos per picker for the week and flags anyone under the quota. Thousands of weighings become one row per name.

The Code

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);
""")
rows = db.execute("""
  SELECT picker, SUM(kilos) AS total, COUNT(*) AS days
  FROM pluck GROUP BY picker HAVING SUM(kilos) >= 20
""").fetchall()
print(rows)
# [('Meena', 40.0, 2), ('Suri', 30.0, 1)]

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE pluck (picker TEXT, kilos REAL);
INSERT INTO pluck VALUES ('Meena', 18.0), ('Ravi', 9.5), ('Meena', 22.0);
""")
print(db.execute("SELECT COUNT(*) FROM pluck GROUP BY picker").fetchall())