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.