Lesson 6 of 8 · 5 min
Grouping and aggregates
COUNT, SUM, AVG, MIN and MAX, GROUP BY and HAVING, subqueries, common table expressions, and a first look at window functions.
On this page
Aggregate functions turn many rows into one value. Combined with GROUP BY, they answer questions like "how much did each customer spend?".
Aggregate functions
SELECT
COUNT(*) AS products,
MIN(price) AS cheapest,
MAX(price) AS most_expensive,
AVG(price) AS average_price,
SUM(price) AS total_list_price
FROM products;
COUNT(*)counts rows.COUNT(city)counts rows wherecityis notNULL.COUNT(DISTINCT city)counts different cities.SUM,AVG,MIN,MAXignoreNULLvalues.
GROUP BY
GROUP BY splits rows into groups and runs the aggregate once per group:
SELECT category, COUNT(*) AS products, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY products DESC;
| category | products | avg_price |
|---|---|---|
| Accessories | 3 | 2832.33 |
| Displays | 1 | 18999.00 |
| Video | 1 | 3499.00 |
The rule: every column in SELECT must either be in GROUP BY or be inside an aggregate. Otherwise the database doesn't know which row's value to show.
Revenue per customer
Combine a join with grouping:
SELECT
c.name,
COUNT(DISTINCT o.id) AS orders,
SUM(oi.quantity * p.price) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC;
COUNT(DISTINCT o.id) is needed because the join to order_items repeats each order once per item.
HAVING
WHERE filters rows before grouping. HAVING filters groups after:
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;
Customers with at least two orders.
Subqueries
A query can use the result of another query:
-- Products that cost more than the average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Customers who have at least one pending order
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending');
Common table expressions (WITH)
A CTE names a subquery so you can read a long query top to bottom:
WITH order_totals AS (
SELECT oi.order_id, SUM(oi.quantity * p.price) AS total
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY oi.order_id
)
SELECT o.id, c.name, t.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_totals t ON t.order_id = o.id
ORDER BY t.total DESC;
Window functions
A window function calculates across related rows without collapsing them into one. Each row keeps its identity and gains an extra value:
SELECT
name,
category,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category,
AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;
PARTITION BY is like GROUP BY for the window. Other useful ones: ROW_NUMBER(), LAG(col) (the previous row's value) and running totals with SUM(col) OVER (ORDER BY date). They work in PostgreSQL, MySQL 8+ and SQLite 3.25+.
Check yourself
- How many orders are there in each status?
- What is the total value of each order? Which order is the largest?
- Which product category brought in the most revenue from non-cancelled orders?
More from Databases and SQL
- Lesson 8 · 6 minIndexes and schema designHow indexes make queries fast and what they cost, reading a query plan, normalisation in plain words, and a checklist for designing tables.
- Lesson 1 · 6 minWhat is a database?Tables, rows and columns, primary and foreign keys, relational versus NoSQL databases, and what SQL actually is.
- Lesson 2 · 6 minCreating tablesCREATE TABLE, choosing data types, constraints that keep data valid, and building the shop database used for the rest of the course.