Aggregates — What GROUP BY Actually Does

In a nutshell

GROUP BY folds rows into groups, and after folding, only values that represent the group remain. So the SELECT list can contain only the group keys, values that hang on the keys, and aggregate functions. Aggregation is an operation that goes wrong without an error, so before trusting a result, check that the totals before and after folding match.

Why this was needed

We got a message that the monthly report's revenue by channel differed by 3% from the finance team's numbers. The query ran without errors and the numbers looked plausible. When we took it apart, there were three causes. Return slips were mixed in as negative quantities; the month was cut using the server time zone (UTC), so Korean orders from 0:00 to 9:00 on the 1st of each month had slipped into the previous month; and the order table was joined with the order line table and then the order amount was added, so the amount of orders with several products had been added several times.

Questions such as "revenue by channel" or "number of orders by status" want a summary rather than individual rows. Aggregation is an operation that folds several rows into one, and the criterion for folding is GROUP BY. After folding, the original rows are no longer visible, so a wrongly folded result like the one above cannot be told apart by appearance. So you must learn how to check together with how it works.

How it works

If you remember the processing order, most of the confusing parts are resolved.

FROM -> WHERE -> GROUP BY -> aggregate -> HAVING -> SELECT -> ORDER BY -> LIMIT

• WHERE is applied before folding, and HAVING after folding. So you cannot write WHERE count(*) > 5, because at that point there is nothing to count yet. It is better to put row-level conditions in WHERE. The rows to be folded themselves decrease, so there is less work.
• In PostgreSQL, an alias given in SELECT can be used in ORDER BY and GROUP BY but not in WHERE and HAVING. There you must write out the expression again.
• Columns in the SELECT list must either be in GROUP BY or be inside an aggregate function. There is one exception. If you grouped by a table's primary key, the other columns of that table are functionally dependent on the key and have only one value per group, so PostgreSQL accepts those columns even if you do not list them in GROUP BY.

Aggregate functions ignore NULL. avg(price) also removes rows with NULL from the denominator. count(*) counts all rows, but count(price) counts only rows where price is not NULL. And if there are no rows at all, aggregate functions other than count return NULL, not 0. sum is the same. If you need 0, say so explicitly, as in coalesce(sum(x), 0).

The FILTER clause is used to do different aggregations by condition within the same group. Only rows for which the condition is true go into that aggregate function.

SELECT channel,
       count(*) FILTER (WHERE status = 'paid')      AS paid_count,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders
GROUP BY channel;

To do the same thing with WHERE, you would have to run the query twice and join, and you would also have to handle separately the problem of channels missing on one side dropping out of the result.

In time-based aggregation, you must always pin down the time zone. On a timestamptz column, using date_trunc('month', ordered_at) truncates according to the session's TimeZone setting. That is why the same query gives different answers depending on the tool you connected with. ordered_at AT TIME ZONE 'Asia/Seoul' returns what time it was in Seoul at that moment as a timestamp without a time zone, so if you truncate that, you get the same answer wherever you run it. PostgreSQL also provides a date_trunc that takes the time zone as a third argument.

What goes wrong in the field

Joins inflate rows. If an order has 3 products, the result of joining orders and order lines shows that order as 3 rows. If you add the order amount here, it is added three times. The symptom is that revenue is inflated by the average number of products. Aggregate the order-level amount first in the order table, or fold to the needed unit before joining.

A LEFT JOIN quietly becomes an INNER JOIN. You did a LEFT JOIN based on the customer table to include even customers with no orders in the denominator, but if you put a condition on an order table column in WHERE (o.status = 'paid'), customer rows with no orders become NULL under that condition and are filtered out. You must move the condition to the ON clause. If you use count(*) in the same query, customers with no orders are also counted as 1, so when counting orders use count(o.id).

You add negatives and missing values without knowing. If return slips are in as negative quantities, sum(quantity) quietly reduces the sales volume and the ranking of top products flips. Before aggregating, look at signs and NULLs first.

The month boundary is off by 9 hours. Korean time is 9 hours ahead of UTC. If you cut the month in UTC, Korean orders before 9 a.m. on the 1st of each month are booked as the previous month's revenue. If you add up all the monthly totals you get the same as the total, so it does not show up in a total check, and it differs only slightly each month.

You take an average of averages. If you average the average basket size per store again, a store with 10 orders and a store with 10,000 orders carry the same weight. If you need the overall average, divide the total by the total.

Integer division and the rounding type. In PostgreSQL, dividing integers discards the fractional part. If 100 * a / b comes out as 0 or 99, suspect this and make one side numeric, as in 100.0 * a / b. Also, round(x, 2), which takes the number of decimal places, is defined for numeric, so using it on a double precision value gives an error that the function does not exist. Change it to round(x::numeric, 2).

How to check

Before producing an aggregation result, cross-check four things.

-- 1. fan-out: rows vs distinct keys after the join
SELECT count(*), count(DISTINCT o.id)
FROM orders o JOIN order_items i ON i.order_id = o.id;

-- 2. sign and nulls before summing
SELECT min(quantity), max(quantity), count(*) - count(quantity) AS null_qty
FROM order_items;

-- 3. which time zone this session uses
SHOW TimeZone;

-- 4. the grouped totals must add up to the ungrouped total
SELECT (SELECT sum(total_amount) FROM orders) AS all_rows,
       (SELECT sum(revenue) FROM (SELECT channel, sum(total_amount) AS revenue
                                  FROM orders GROUP BY channel) g) AS by_group;

If the two numbers in the first query differ, the join is inflating rows. If the minimum of the second query is negative or the NULL count is not 0, first decide how to handle those rows. If the two values in the fourth query differ, rows were dropped or overlapped in the process of folding into groups. And when you produce an average or a per-person value, write down together with the result what you put in the denominator. Whether you include or exclude customers with no orders changes the number greatly, and both can be correct answers.

What to see in the next reading

In the reading right after this, you will learn window functions, which compute ranks and running totals without folding rows. Then in the lab you will build, in turn, counts by status, revenue by channel, HAVING and FILTER, monthly aggregation on a Seoul basis, top products excluding negative quantities, and revenue per customer by tier that includes even customers with no orders in the denominator, stepping on this section's traps one by one yourself.