Chapter 4 · Functions and Summaries
Aggregates, GROUP BY and HAVING
- Page 14 of 22
- 14 min read
Nobody asks a database for "all 14 orders". They ask "how many orders did we get?", "what is each customer's total spend?", "which product earns the most?". Each of those collapses many rows into a few numbers: an aggregate. Every dashboard metric is one, and so are many machine-learning features: number of orders per customer, average basket size, days since last purchase. Doing this in SQL, inside the database, means you move a small summary into Python instead of millions of raw rows.
What you will learn
COUNT(*)vsCOUNT(column)vsCOUNT(DISTINCT column), andSUM,AVG,MIN,MAX.- How NULLs and empty groups behave inside aggregates.
GROUP BYon one or several columns, and revenue asSUM(quantity * unit_price).HAVINGvsWHERE, and where they sit in the query's logical order.- The classic mistakes: non-grouped columns, aggregating at the wrong level, and averaging averages.
Aggregate functions over a whole table
Without GROUP BY, an aggregate turns the whole table (after WHERE) into one row. The three kinds of COUNT answer three different questions:
SELECT COUNT(*) AS customers, -- rows
COUNT(city) AS with_city, -- rows where city is not NULL
COUNT(DISTINCT city) AS cities -- different non-NULL values
FROM customers;+-----------+-----------+--------+
| customers | with_city | cities |
+-----------+-----------+--------+
| 8 | 7 | 5 |
+-----------+-----------+--------+Eight customers, but only seven have a city (Sadia's is NULL), and they live in five different cities. SUM, AVG, MIN and MAX also skip NULLs. Look what that does to average stock, where the three NULLs are courses and the gift card:
SELECT COUNT(*) AS products,
COUNT(stock) AS with_stock,
SUM(stock) AS total_stock,
AVG(stock) AS avg_stock,
ROUND(AVG(COALESCE(stock, 0)), 2) AS avg_if_null_were_0,
MIN(price) AS cheapest,
MAX(price) AS dearest
FROM products;+----------+------------+-------------+-----------+--------------------+----------+---------+
| products | with_stock | total_stock | avg_stock | avg_if_null_were_0 | cheapest | dearest |
+----------+------------+-------------+-----------+--------------------+----------+---------+
| 11 | 8 | 165 | 20.625 | 15.0 | 900 | 18500 |
+----------+------------+-------------+-----------+--------------------+----------+---------+AVG(stock) is 165 ÷ 8 = 20.625: the average over the 8 products that track stock. Treating NULL as 0 gives 165 ÷ 11 = 15.0, a different claim. Neither is "right"; decide which question you are asking. The same choice appears in ML when you decide whether a missing value means "zero" or "unknown".
When no rows match at all, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL, not 0. Furniture has no products:
SELECT COUNT(*) AS products,
SUM(price) AS sum_price,
COALESCE(SUM(price), 0) AS sum_or_zero
FROM products
WHERE category_id = 5;+----------+-----------+-------------+
| products | sum_price | sum_or_zero |
+----------+-----------+-------------+
| 0 | NULL | 0 |
+----------+-----------+-------------+GROUP BY: one result row per group
GROUP BY status splits the rows into one pile per status, runs the aggregates on each pile, and returns one row per pile:
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;+-----------+--------+
| status | orders |
+-----------+--------+
| delivered | 11 |
| cancelled | 1 |
| pending | 1 |
| shipped | 1 |
+-----------+--------+Pictured with the order ids:
orders (14 rows) split by status aggregate each pile
----------------- ------------------------- --------------------
1 delivered ──► delivered: 1 2 3 4 6 7 8 ──► delivered 11
2 delivered 9 10 11 14
... cancelled: 5 ──► cancelled 1
13 pending pending: 13 ──► pending 1
14 delivered shipped: 12 ──► shipped 1This is the same split-apply-combine you did with df.groupby() in pandas.
Revenue: SUM(quantity * unit_price)
An order's value lives in order_items: one row per product in the order, with the price actually charged. The aggregate can work on an expression, so the total per order is:
SELECT order_id,
COUNT(*) AS lines,
SUM(quantity) AS units,
SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_id;+----------+-------+-------+-------------+
| order_id | lines | units | order_total |
+----------+-------+-------+-------------+
| 1 | 2 | 2 | 2100 |
| 2 | 1 | 1 | 4500 |
| 3 | 1 | 1 | 3000 |
| 4 | 2 | 2 | 4000 |
| 5 | 1 | 1 | 18500 |
| 6 | 1 | 1 | 15000 |
| 7 | 2 | 3 | 4000 |
| 8 | 1 | 2 | 2400 |
| 9 | 2 | 2 | 4950 |
| 10 | 1 | 1 | 18500 |
| 11 | 2 | 2 | 5500 |
| 12 | 1 | 2 | 3000 |
| 13 | 1 | 1 | 15000 |
| 14 | 2 | 2 | 3100 |
+----------+-------+-------+-------------+Use unit_price from order_items, not today's products.price: order 9 sold the keyboard at a discount (4050), and prices change over time. Grouping by product instead tells you which products earn the most:
SELECT product_id,
SUM(quantity) AS units,
SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY product_id
ORDER BY revenue DESC, product_id
LIMIT 5;+------------+-------+---------+
| product_id | units | revenue |
+------------+-------+---------+
| 5 | 2 | 37000 |
| 7 | 2 | 30000 |
| 3 | 2 | 8550 |
| 6 | 2 | 6000 |
| 2 | 2 | 5000 |
+------------+-------+---------+These figures include every order line, even cancelled order 5 and pending order 13. Keeping only delivered orders needs the
statuscolumn fromorders, and showing product names needsproducts: both are joins, coming in Joins: INNER JOIN and Table Aliases.
Grouping by several columns
List several columns and you get one row per combination that occurs. Orders per customer and status:
SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;+-------------+-----------+--------+
| customer_id | status | orders |
+-------------+-----------+--------+
| 1 | delivered | 3 |
| 2 | delivered | 2 |
| 2 | shipped | 1 |
| 3 | delivered | 3 |
| 4 | cancelled | 1 |
| 5 | delivered | 1 |
| 6 | delivered | 1 |
| 6 | pending | 1 |
| 7 | delivered | 1 |
+-------------+-----------+--------+Only combinations that exist appear: customer 8, who never ordered, has no row at all (finding such "missing" rows is what LEFT JOIN is for; see LEFT, RIGHT, FULL, CROSS and Self Joins).
HAVING vs WHERE
WHERE filters rows before grouping; HAVING filters groups after the aggregates are computed. Customers with at least three orders:
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;+-------------+--------+
| customer_id | orders |
+-------------+--------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+-------------+--------+Using both: count only delivered orders (WHERE), then keep customers with at least three of them (HAVING). Customer 2 drops out, because one of his three orders is only shipped:
SELECT customer_id, COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
| 1 | 3 |
| 3 | 3 |
+-------------+------------------+An aggregate cannot go in WHERE, because when WHERE runs, the groups do not exist yet:
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE COUNT(*) >= 3
GROUP BY customer_id;Error: misuse of aggregate: COUNT()The logical order explains all of this: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Since SELECT comes after HAVING, a column alias like orders officially does not exist yet in HAVING. SQLite and MySQL let you use it anyway; PostgreSQL does not:
-- PostgreSQL
SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
HAVING n >= 3;Error: column "n" does not existRepeat the aggregate (HAVING COUNT(*) >= 3) and the query runs everywhere.
Columns that are not grouped
Every column in SELECT must either be in GROUP BY or be inside an aggregate. Otherwise the question has no single answer: customer 1 has three order dates, so which one belongs on her one row? PostgreSQL and MySQL 8 refuse:
-- PostgreSQL
SELECT customer_id, order_date, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;Error: column "orders.order_date" must appear in the GROUP BY clause or be used in an aggregate function-- MySQL
SELECT customer_id, order_date, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;Error: ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.order_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_bySQLite runs it and quietly picks a date from some row of each group:
SELECT customer_id, order_date, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id
LIMIT 3;+-------------+------------+--------+
| customer_id | order_date | orders |
+-------------+------------+--------+
| 1 | 2026-01-12 | 3 |
| 2 | 2026-01-20 | 3 |
| 3 | 2026-02-14 | 3 |
+-------------+------------+--------+Here it happened to be each customer's first order, but SQLite promises nothing; it could change with an index or a new version. Say what you mean: MIN(order_date) AS first_order or MAX(order_date) AS last_order.
Aggregating at the right level
"What is our average order value?" Here are two answers from the same table:
SELECT SUM(quantity * unit_price) AS gross,
COUNT(DISTINCT order_id) AS orders,
ROUND(AVG(quantity * unit_price), 2) AS avg_line_value,
ROUND(SUM(quantity * unit_price) * 1.0
/ COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM order_items;+--------+--------+----------------+-----------------+
| gross | orders | avg_line_value | avg_order_value |
+--------+--------+----------------+-----------------+
| 103550 | 14 | 5177.5 | 7396.43 |
+--------+--------+----------------+-----------------+AVG(quantity * unit_price) averages over the 20 lines, not the 14 orders, so it answers a different question. Average order value is total ÷ number of orders. Before you write an average, ask: one row per what?
The same table also lets you reconcile with the money that actually came in. The 103,550 taka ordered includes cancelled order 5 (18,500) and unpaid pending order 13 (15,000); take those away and you get 70,050, exactly what the payments table holds:
SELECT COUNT(*) AS payments, SUM(amount) AS received FROM payments;+----------+----------+
| payments | received |
+----------+----------+
| 13 | 70050 |
+----------+----------+Checking that two independent totals agree is how data teams catch broken pipelines.
Never average the averages
Average price per category, then the average of those, is not the average price:
import pandas as pd
from sqlhelp import con
by_cat = pd.read_sql_query("""
SELECT category_id, COUNT(*) AS products, AVG(price) AS avg_price
FROM products
GROUP BY category_id
ORDER BY category_id
""", con)
print(by_cat)
print("average of the averages:", by_cat["avg_price"].mean())
print("true average price: ", round(pd.read_sql_query("SELECT AVG(price) AS a FROM products", con)["a"][0], 2)) category_id products avg_price
0 NaN 1 1000.0
1 1.0 2 1850.0
2 2.0 4 7850.0
3 3.0 2 9000.0
4 4.0 2 1850.0
average of the averages: 4310.0
true average price: 5254.55The average of averages gives the lone gift card (the NaN category, which is NULL in SQL) the same weight as four electronics. To combine group averages correctly, weight them by their counts, or go back to the raw rows. Notice also that GROUP BY kept NULL as a group of its own.
Dialect notes
| SQLite | MySQL | PostgreSQL | |
|---|---|---|---|
AVG of integers (card payments: AVG(amount)) | Float (7491.666666666667) | Decimal, 4 places (7491.6667) | Numeric, many places (7491.6666666666666667) |
| Non-grouped column | Allowed, arbitrary row | Error (ONLY_FULL_GROUP_BY, default since 5.7) | Error |
Alias in HAVING | Allowed | Allowed | Error |
| Sum of nothing | NULL (TOTAL() gives 0.0) | NULL | NULL |
So wrap averages in ROUND(…, 2) when you want the same output everywhere.
Where this shows up in real systems
- Customer features for ML:
MAX(order_date),COUNT(*)andSUM(amount)per customer give the R, F and M of RFM (recency, frequency, monetary), the classic churn and lifetime-value features. - Model evaluation: with a 0/1
is_correctcolumn,AVG(is_correct)grouped by model is accuracy per model. In SQLite and MySQL a comparison is already 0/1, soSELECT AVG(status = 'delivered') FROM ordersgives the delivered rate. - LLM cost monitoring:
SUM(tokens)andCOUNT(*)per day and model, withHAVINGto flag days over budget.
Common mistakes
- Selecting a column that is not grouped. Errors on the servers, random values in SQLite. Fix: add it to
GROUP BY, or wrap it:MAX(order_date). - An aggregate in WHERE.
misuse of aggregate. Fix: move that condition toHAVING; keep plain row filters inWHERE(it is faster too, because fewer rows get grouped). COUNT(column)when you meant rows. It skips NULLs:COUNT(city)is 7, not 8. Fix:COUNT(*)for rows;COUNT(column)only for "rows that have a value".- Averaging at the wrong level, or averaging averages. Fix: decide what one row means (line, order, customer), then compute
SUM(…) / COUNT(DISTINCT …)at that level. - Forgetting that SUM of nothing is NULL. A report shows a blank instead of 0. Fix:
COALESCE(SUM(x), 0).
Try it yourself
- Easy: For each
category_idin products, show the number of products and the average price rounded to whole taka. Order bycategory_id. - Medium: For each customer with at least two orders, show the number of orders, the first order date and the last order date. Order by number of orders (most first), then
customer_id. - Hard: Find the orders that contain more than one product and are worth at least 4000 taka. Show lines, units and the order total, largest total first (ties by
order_id). Then, per payment method, show how many payments, the total and the average amount rounded to 2 places, keeping only methods with a total over 10,000.
Answers
-- Easy
SELECT category_id, COUNT(*) AS products, ROUND(AVG(price)) AS avg_price
FROM products
GROUP BY category_id
ORDER BY category_id;+-------------+----------+-----------+
| category_id | products | avg_price |
+-------------+----------+-----------+
| NULL | 1 | 1000.0 |
| 1 | 2 | 1850.0 |
| 2 | 4 | 7850.0 |
| 3 | 2 | 9000.0 |
| 4 | 2 | 1850.0 |
+-------------+----------+-----------+-- Medium
SELECT customer_id,
COUNT(*) AS orders,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY orders DESC, customer_id;+-------------+--------+-------------+------------+
| customer_id | orders | first_order | last_order |
+-------------+--------+-------------+------------+
| 1 | 3 | 2026-01-12 | 2026-04-19 |
| 2 | 3 | 2026-01-20 | 2026-05-21 |
| 3 | 3 | 2026-02-14 | 2026-06-09 |
| 6 | 2 | 2026-04-02 | 2026-06-02 |
+-------------+--------+-------------+------------+-- Hard
SELECT order_id,
COUNT(*) AS lines,
SUM(quantity) AS units,
SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1 AND SUM(quantity * unit_price) >= 4000
ORDER BY order_total DESC, order_id;
SELECT method, COUNT(*) AS payments, SUM(amount) AS total, ROUND(AVG(amount), 2) AS avg_amount
FROM payments
GROUP BY method
HAVING SUM(amount) > 10000
ORDER BY total DESC;+----------+-------+-------+-------------+
| order_id | lines | units | order_total |
+----------+-------+-------+-------------+
| 11 | 2 | 2 | 5500 |
| 9 | 2 | 2 | 4950 |
| 4 | 2 | 2 | 4000 |
| 7 | 2 | 3 | 4000 |
+----------+-------+-------+-------------+
+--------+----------+-------+------------+
| method | payments | total | avg_amount |
+--------+----------+-------+------------+
| card | 6 | 44950 | 7491.67 |
| bkash | 5 | 18000 | 3600.0 |
+--------+----------+-------+------------+Summary
COUNT(*)counts rows,COUNT(col)non-NULL values,COUNT(DISTINCT col)different values;SUM/AVG/MIN/MAXskip NULLs, and return NULL for no rows.GROUP BYgives one row per group (or per combination of columns); every selected column must be grouped or aggregated.- Revenue is
SUM(quantity * unit_price)from the line items, at the price actually charged. WHEREfilters rows before grouping,HAVINGfilters groups after; order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.- Know the level of every average, never average averages, and reconcile totals from independent tables.
Next: CASE WHEN, Conditional Counts and Business Questions. You will add if-then logic to queries with CASE, put rows into buckets, count several conditions in one pass, group by month, find duplicates, and answer real business questions about the shop.