←Back to the bookChapter 154 · Notebook 2 · Aggregation

▶ Open in Colab⬇ Download notebookView on GitHub

Chapter 154 · Tools & Workflow · Notebook 2 of 5

Aggregation: COUNT, SUM, GROUP BY

A database is most useful when it summarizes. Aggregate functions collapse many rows into one number, and GROUP BY computes that number separately for each group. This is the SQL version of the split-apply-combine you already know.

import pandas as pd, sqlite3
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "sql-and-databases--shop.xlsx"
try:
    xls = pd.ExcelFile("../../data/" + fn)
except FileNotFoundError:
    xls = pd.ExcelFile(BASE + fn)
# Load the four sheets into an in-memory SQLite database, then query it with real SQL.
con = sqlite3.connect(":memory:")
for table in ["customers", "products", "orders", "order_items"]:
    pd.read_excel(xls, sheet_name=table).to_sql(table, con, index=False, if_exists="replace")

def q(sql):
    """Run a SQL query against the shop database and return the result as a DataFrame."""
    return pd.read_sql_query(sql, con)

print("tables:", [r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")])
q("SELECT * FROM products LIMIT 3")

tables: ['customers', 'products', 'orders', 'order_items']

	product_id	product	category	unit_price
0	1	Cold Brew Kit	Equipment	39
1	2	Pour-Over Set	Equipment	28
2	3	Espresso Beans 1kg	Beans	22

DEMO 1 · Aggregate the whole table

COUNT, SUM, AVG, MIN, and MAX reduce a column to a single value. COUNT(*) counts rows; COUNT(DISTINCT x) counts unique values.

q("""SELECT COUNT(*)                AS line_items,
              COUNT(DISTINCT order_id) AS orders,
              ROUND(SUM(revenue), 2)   AS total_revenue,
              ROUND(AVG(revenue), 2)   AS avg_line,
              MAX(revenue)             AS biggest_line
       FROM order_items""")

	line_items	orders	total_revenue	avg_line	biggest_line
0	1494	600	114592.4	76.7	595.0

DEMO 2 · GROUP BY: one summary per group

GROUP BY splits the rows by a column and computes the aggregate for each group. Here, revenue per product id, sorted to reveal the best sellers.

q("""SELECT product_id,
              COUNT(*)              AS lines,
              SUM(quantity)         AS units,
              ROUND(SUM(revenue),2) AS revenue
       FROM order_items
       GROUP BY product_id
       ORDER BY revenue DESC
       LIMIT 5""")

	product_id	lines	units	revenue
0	12	166	478	20722.50
1	1	165	501	18879.90
2	8	39	130	14833.35
3	3	195	593	12521.30
4	2	140	418	11198.60

DEMO 3 · HAVING: filter the groups

WHERE filters rows before grouping; HAVING filters the groups after aggregating. Use HAVING when the condition is about the summary, such as ‘products that sold more than 120 units’.

q("""SELECT product_id, SUM(quantity) AS units
       FROM order_items
       GROUP BY product_id
       HAVING SUM(quantity) > 120
       ORDER BY units DESC""")

	product_id	units
0	3	593
1	4	536
2	1	501
3	12	478
4	9	460
5	5	423
6	2	418
7	10	277
8	6	262
9	11	221
10	7	184
11	8	130

DEMO 4 · WHERE and HAVING together

They cooperate: WHERE trims the rows first, then GROUP BY and HAVING work on what remains. This counts only full-price lines, then keeps the busy products.

q("""SELECT product_id,
              COUNT(*)              AS full_price_lines,
              ROUND(SUM(revenue),2) AS revenue
       FROM order_items
       WHERE discount_pct = 0
       GROUP BY product_id
       HAVING COUNT(*) >= 25
       ORDER BY revenue DESC""")

	product_id	full_price_lines	revenue
0	12	114	14265.0
1	1	110	13104.0
2	3	123	7854.0
3	2	84	6776.0
4	5	87	5187.0
5	4	124	4440.0
6	7	43	4182.0
7	6	56	4032.0
8	11	39	3025.0
9	9	94	1752.0
10	10	60	1422.0

DEMO 5 · Group by more than one column

GROUP BY several columns to summarize each combination. Discounts by size show how often each discount tier was used and what it brought in.

q("""SELECT discount_pct,
              COUNT(*)              AS lines,
              ROUND(SUM(revenue),2) AS revenue
       FROM order_items
       GROUP BY discount_pct
       ORDER BY discount_pct""")

	discount_pct	lines	revenue
0	0	955	74250.00
1	5	220	15801.35
2	10	177	14559.30
3	15	90	6489.75
4	20	52	3492.00

Wrap-up

Aggregate functions collapse a column to one value; GROUP BY does it per group; WHERE filters rows before, HAVING filters groups after. That is the entire grammar of a summary query, and it maps one to one onto a pandas groupby. But the real power of a relational database is spreading data across tables, which means the next step is the join.

←Back to the bookChapter 154 · Notebook 2 · Aggregation