Aggregation & Business Metrics

A function takes input and gives back a result, like LOWER in chapter 1. An aggregate function reads many rows and gives back one value. COUNT and SUM are two of them.

Chapter 1 used COUNT on a whole table. This query uses COUNT and SUM together:

SELECT COUNT(*)      AS orders,
       SUM(amount)   AS total_amount
FROM orders;

SUM skips order 105, because its amount is NULL. The total is 40 + 25 + 60 + 15.50 + 80.

What you will learn

Many business numbers are aggregates: orders per day, revenue per country, the share of paid orders. A small mistake gives a wrong number and no error message.

In this chapter, you learn to:

1. Use COUNT, SUM, AVG, MIN and MAX, and know what they do with NULL.
2. Make one result row for each group with GROUP BY.
3. Remove groups with HAVING.
4. Count different values, and count only some rows.
5. Calculate a rate without a wrong answer or an error.
6. Find the median, and check that a grouped result is right.

The examples use the same shop as chapter 1: customers, orders and order_items. They use PostgreSQL. We tell you when another database is different.

SQL has five main aggregate functions. This table shows what each one gives:

Order 105 has no amount, so COUNT(amount) is 5. Here are all of them in one query. COUNT(*) is in it too, so you can compare the two counts:

SELECT COUNT(*)                AS all_orders,
       COUNT(amount)           AS with_amount,
       SUM(amount)             AS total,
       ROUND(AVG(amount), 2)   AS average,
       MIN(amount)             AS smallest,
       MAX(amount)             AS largest
FROM orders;

Order 105 has no amount. COUNT(*) counts it, so you get 6. The other functions skip it, so they use only 5 amounts.

AVG divides by the amounts it sees

AVG adds the 5 amounts and divides by 5. It does not divide by 6. Compare it with SUM divided by COUNT(*):

SELECT ROUND(AVG(amount), 2)          AS skips_null,
       ROUND(SUM(amount) / COUNT(*), 2) AS counts_all_rows
FROM orders;

So far, one query gave one row for the whole table. GROUP BY gives one row for each group. A group is a set of rows that share the same value in a column.

Here you put the orders into groups by status. Then you ask two questions about each group:

SELECT status,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY status
ORDER BY status;

The database made three groups. Look at the same six orders, sorted into those groups:

Step 2: COUNT and SUM turn each group into one row:

• paid: 4 orders. 40 + 25 + 15.50 + 80 = 160.50.
• pending: 1 order. The amount is NULL, so SUM gives NULL, not 0.
• refunded: 1 order. The amount is 60.00.

The rule for SELECT

Every column in SELECT must be in GROUP BY, or inside an aggregate function. A group holds many rows, so a plain column can have many values.

Try to show status while you group by customer_id. Customer 3 has two statuses, so the database cannot pick one. The query fails:

SELECT customer_id, status, SUM(amount)
FROM orders
GROUP BY customer_id;
-- ERROR: column "orders.status" must appear in the GROUP BY clause
--        or be used in an aggregate function

To fix it, remove status from SELECT:

SELECT customer_id,
       SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

This is the other fix for the error in the last section: add status to GROUP BY. You then group by two columns, and a group is one combination of values.

Group by customer_id and status. Each pair of customer and status gets its own row:

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

Customer 1 has two paid orders, so they form one group: 2 orders, 65.00. Customer 3 now has two rows, one paid and one pending. The result has 5 rows, not 4.

The grain of this result is one row per customer and status. Chapter 1 defined grain: it is what one row means.

Compare how many rows each choice gives on our 6 orders:

WHERE removes rows before grouping. HAVING removes groups after grouping. Use HAVING when the test needs an aggregate, such as SUM or COUNT.

Keep only the customers whose total is at least 60:

SELECT customer_id,
       SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 60
ORDER BY customer_id;

Customer 3 has a total of 15.50, so HAVING removes that group. Customer 2 has exactly 60.00, and >= keeps it.

Use WHERE and HAVING together

WHERE runs first. Here it keeps only paid orders. Then HAVING keeps customers with at least two of them:

SELECT customer_id,
       COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

An aggregate in WHERE fails

WHERE cannot use SUM or COUNT. This query puts SUM in WHERE, and it fails:

SELECT customer_id, SUM(amount)
FROM orders
WHERE SUM(amount) >= 60
GROUP BY customer_id;
-- ERROR: aggregate functions are not allowed in WHERE

The reason is the order of the work. The database runs the clauses in this order:

1. FROM: choose the table.
2. WHERE: keep rows.
3. GROUP BY: make the groups.
4. HAVING: keep groups.
5. SELECT: choose the columns.
6. ORDER BY: sort the result.

When WHERE runs, no group exists yet, so no sum exists yet. For the same reason, PostgreSQL cannot use a name from SELECT inside HAVING. Write SUM(amount) again.

Chapter 1 showed COUNT(DISTINCT column). It counts different values and skips NULL. Now you use it inside groups.

Count the orders, and the different customers, for each status:

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

Paid has 4 orders but only 3 customers. Customer 1 placed two of the paid orders, 101 and 102.

COUNT(*) counts rows, which here are orders. COUNT(DISTINCT customer_id) counts people. Before you write the query, decide which one you want.

Customers who ordered

How many customers placed at least one order? The customers table has 5 rows, but Omar never ordered:

SELECT COUNT(DISTINCT customer_id) AS customers_with_orders
FROM orders;

Sometimes you want several numbers side by side in one row. For example: paid money and refunded money for each customer. Put a CASE inside an aggregate function.

CASE gives a value when a test is true. Here, SUM adds the amount when the status matches. When it does not match, SUM adds 0:

SELECT customer_id,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)
         AS paid_amount,
       SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END)
         AS refunded_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Look at customer 3. Order 104 is paid, so 15.50 goes into paid_amount. Order 105 is pending, so it adds 0 to both columns.

Count only some rows

To count rows, leave out ELSE. A CASE with no ELSE gives NULL when the test is false, and COUNT skips NULL:

SELECT COUNT(CASE WHEN status = 'paid'     THEN 1 END) AS paid,
       COUNT(CASE WHEN status = 'pending'  THEN 1 END) AS pending,
       COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded
FROM orders;

FILTER: a shorter form

PostgreSQL has a shorter form, called FILTER. It does the same job as CASE inside an aggregate:

SELECT COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;

A rate is one number divided by another. The paid rate is paid orders divided by all orders. Two mistakes can make a rate wrong.

Mistake 1: whole-number division

In our shop, 4 of 6 orders are paid. Chapter 1 showed that 4 / 6 gives 0 in PostgreSQL. COUNT also gives whole numbers, so the same thing happens here:

SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END) / COUNT(*) AS wrong_rate,
       ROUND(100.0 * COUNT(CASE WHEN status = 'paid' THEN 1 END)
             / COUNT(*), 1)                                   AS paid_percent
FROM orders;

Round at the end, only to show the number. If you round early, every later step uses the rounded number.

Mistake 1 in a grouped query

Use the same rate in a grouped query. The CASE gives 1 for a paid order and NULL for the others. COUNT counts the 1s:

SELECT customer_id,
       ROUND(100.0 * COUNT(CASE WHEN status = 'paid' THEN 1 END)
             / COUNT(*), 1) AS paid_percent
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Mistake 2: dividing by zero

Dividing by zero is an error in PostgreSQL:

SELECT 10 / 0;
-- ERROR: division by zero

NULLIF(a, b) gives NULL when a equals b. In all other cases, it gives a. So NULLIF(x, 0) turns a zero into NULL, and dividing by NULL gives NULL:

SELECT NULLIF(0, 0) AS same,
       NULLIF(5, 0) AS different;

Omar has no orders. A LEFT JOIN, from chapter 2, keeps him in the result. COUNT(*) would count his NULL row as 1 order.

COUNT(o.order_id) skips it, so Omar has 0 orders. Show the paid rate for every customer, including Omar:

SELECT c.name,
       COUNT(o.order_id) AS orders,
       ROUND(100.0 * COUNT(CASE WHEN o.status = 'paid' THEN 1 END)
             / NULLIF(COUNT(o.order_id), 0), 1) AS paid_percent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Keep the NULL. A rate of 0 would say “he had orders and none were paid”. That is not true for Omar.

Some databases, such as MySQL, return NULL for a division by zero. NULLIF gives the same result everywhere.

The average can be a poor summary. One large value makes it bigger. The median is the middle value after you sort the values.

Take the four paid amounts, sorted from small to big:

There are 4 values, so there is no single middle one. The median is the average of the two middle values: (25.00 + 40.00) ÷ 2 = 32.50. With an odd count, the median is the one middle value.

PERCENTILE_CONT(0.5) finds the median in PostgreSQL. Run it next to the average:

SELECT ROUND(AVG(amount), 2) AS average,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders
WHERE status = 'paid';

The big order 106 (80.00) makes the average bigger than the median.

Percentiles

A percentile is a value that a share of the rows are at or below. The p75 is the value that 75% of the rows are at or below. The median is the p50.

WITHIN GROUP (ORDER BY amount) says which values to sort. PostgreSQL has two kinds of percentile. PERCENTILE_CONT (continuous) blends the two nearest values.

PERCENTILE_DISC (discrete) returns a value that is really in the table. Example: the p75 of 4 values has 3 of the 4 at or below it. Run both:

SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS p75_blended,
       PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amount) AS p75_real
FROM orders
WHERE status = 'paid';

Use PERCENTILE_DISC when you need a value that exists. For example: “3 of 4 paid orders are 40.00 or less”.

Other databases

PostgreSQL, Snowflake and Oracle have PERCENTILE_CONT. MySQL and SQLite do not.

There, you sort the rows and pick the middle one with a window function. Chapter 5 teaches window functions.

A grouped query often gives a wrong number with no error. Three traps cause most of these mistakes.

Trap 1: a NULL group

GROUP BY puts all NULL values into one group. Dev has no country, so Dev gets his own row:

SELECT country,
       COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY country;

A NULL row is easy to miss in a long report. COALESCE(a, b) gives b when a is NULL, as in chapter 1. Use it to give the group a name:

SELECT COALESCE(country, 'unknown') AS country,
       COUNT(*)                     AS customers
FROM customers
GROUP BY COALESCE(country, 'unknown')
ORDER BY MIN(customer_id);

Trap 2: an average of averages

Here is the average amount for each customer, with the number of amounts behind it:

SELECT customer_id,
       ROUND(AVG(amount), 2) AS avg_amount,
       COUNT(amount)         AS amounts
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

The average of these four averages is (32.50 + 60.00 + 15.50 + 80.00) ÷ 4 = 47.00. The real average of the five amounts is 44.10.

The numbers differ because customer 1 has two amounts and the others have one. In an average of averages, a customer with 2 amounts counts the same as a customer with 1. To get the right answer, average the amounts, not the averages.

Trap 3: a join repeats rows

Chapter 2 showed that a join can repeat rows. Then SUM adds the same amount many times. Order 101 has two items, so join orders to order_items and add up the amounts:

SELECT SUM(o.amount) AS joined_total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id;

Orders 101 (40.00) and 106 (80.00) each have two items. So each appears twice, and 120.00 is added by mistake.

The fix is to sum amount from orders alone. Chapter 4 shows how to make one row per order before you join.

A metric is a number the business tracks, such as revenue or active customers. Two people can write two correct queries and get two different numbers. They defined the metric in two ways.

Take “active customers” in our shop. Definition A is a customer with at least one order:

-- Active = a customer with at least one order.
SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM orders;

Definition B is a customer with at least one paid order:

-- Active = a customer with at least one paid order.
SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM orders
WHERE status = 'paid';

Neither number is wrong. They answer different questions. So the definition must be written down.

Four parts of a definition

Write these four parts before you write SQL. This table fills them in for Definition B:

Put the definition in a comment above the query, like the examples above. Then the next person gets the same number as you.

A grouped query can run and still be wrong. Do one quick check: for counts and sums, the groups should add up to the whole table. If they do not, rows were lost or repeated.

(COUNT(DISTINCT) and averages do not add up like this.)

The whole orders table has 6 orders and a total of 220.50. Now group the customers by country, with a join:

SELECT COALESCE(c.country, 'unknown') AS country,
       COUNT(o.order_id)               AS orders,
       SUM(o.amount)                   AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY COALESCE(c.country, 'unknown')
ORDER BY MIN(c.customer_id);

Add the orders: 2 + 1 + 2 + 1 = 6. Add the revenue: 65.00 + 60.00 + 15.50 + 80.00 = 220.50. Both match the whole table, so no rows were lost or repeated.

When the check fails

Now join order_items and count the orders for each status:

SELECT o.status,
       COUNT(*) AS orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.status
ORDER BY o.status;

The parts add up to 7, but there are 6 orders. Orders 101 and 106 have two items each, so paid shows 6, not 4. Order 105 has no items, so the pending group is missing.

Here COUNT(*) counted item rows, not orders. To count orders, use COUNT(DISTINCT o.order_id). To keep order 105, start from orders and LEFT JOIN order_items.

Use this checklist for every grouped result:

1. Do the counts add up to the count of the whole table?
2. Do the sums add up to the sum of the whole table?
3. Is a group missing that you expected?
4. Is there a NULL group?
5. Check one group by hand against the rows.

Each exercise opens in the SQL workspace with its own tables. Write the query, run it, and check the result.

They follow the order of this chapter. The last three use window functions, so try them after chapter 5.