Aggregate Functions and GROUP BY

Analysis is mostly summarising: how many, how much, on average. Aggregate functions turn many rows into one value.

The five core functions

SELECT COUNT(*)   AS products,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       AVG(price) AS average_price,
       SUM(price) AS total_of_prices
FROM products;

COUNT and NULL

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts different values.

SELECT COUNT(*)             AS customers,
       COUNT(city)          AS with_city,
       COUNT(DISTINCT city) AS different_cities
FROM customers;

All aggregate functions except COUNT(*) ignore NULL.

GROUP BY

GROUP BY splits the rows into groups and calculates the aggregate for each one.

SELECT category,
       COUNT(*)   AS products,
       AVG(price) AS average_price
FROM products
GROUP BY category;

The rule: every column in the SELECT list must either be in the GROUP BY clause or be inside an aggregate function. Most databases reject a query that breaks this rule. SQLite allows it and returns a value from an arbitrary row, which is rarely what you want.

Grouping by more than one column

SELECT customer_id,
       status,
       COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id;

Filtering groups with HAVING

WHERE filters rows before they are grouped. HAVING filters the groups afterwards, so it can use aggregates.

SELECT customer_id,
       COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Use both together when you need to:

SELECT customer_id,
       COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY delivered_orders DESC;

Rounding

SELECT category,
       ROUND(AVG(price), 2) AS average_price
FROM products
GROUP BY category;

The order a query is processed in

You write a query in one order, but the database works through it in another:

1. FROM      pick the table
2. WHERE     filter rows
3. GROUP BY  form groups
4. HAVING    filter groups
5. SELECT    calculate the output columns
6. ORDER BY  sort
7. LIMIT     cut

This explains why WHERE cannot use an aggregate (the groups do not exist yet) and why ORDER BY can use an alias from SELECT.

Practice

For each order, show the number of different products and the total quantity of items, using the order_items table.