How do I filter on the result of an aggregate, and what are the options?
You use the HAVING clause, or you put the aggregate in a subquery and filter on the result. There are three options and they are not all the same.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 250.00),
(2, 1, 120.00),
(3, 2, 900.00);
-- Option one: the HAVING clause.
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;
That is the option you will use most of the time, because it is the shortest and it says what it means.
-- Option two: a subquery in the FROM clause.
SELECT customer_id, total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS totals
WHERE total > 100;
-- Option three: a common table expression, which is the same thing
-- with a name that you can read.
WITH totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total
FROM totals
WHERE total > 100;
So what is the difference between the second and the third? Only the name. A common table expression gives the subquery a name, and that name can be used again further down the query, which is handy when you have more than one step.
And when you need something more than a number, there is a fourth option. A window function lets you filter on a calculation over the whole set without grouping at all.
-- Every order, with the customer total beside it.
SELECT o.id, o.customer_id, o.amount,
SUM(o.amount) OVER (PARTITION BY o.customer_id) AS customer_total
FROM orders o
ORDER BY o.customer_id, o.id;
The next question in this topic asks about the speed of all of this.