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
  1. Aggregate functions
  2. GROUP BY
  3. Revenue per customer
  4. HAVING
  5. Subqueries
  6. Common table expressions (WITH)
  7. Window functions
  8. Check yourself

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 where city is not NULL.
  • COUNT(DISTINCT city) counts different cities.
  • SUM, AVG, MIN, MAX ignore NULL values.

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

  1. How many orders are there in each status?
  2. What is the total value of each order? Which order is the largest?
  3. Which product category brought in the most revenue from non-cancelled orders?

More from Databases and SQL