Aggregates, GROUP BY and HAVING

Until now every query returned one result row for each input row. A report is different: "how many orders per customer", "revenue per month", "average price per category". You are not listing rows, you are summarising them. That is what this lesson teaches. It uses the shop from lesson 0 and the joins from lesson 1.

Words used in this lesson

• An aggregate function takes many values and returns one: a count, a sum, an average, a minimum, a maximum.
• A group is the set of rows that share the same value in the GROUP BY columns.
• GROUP BY splits the rows into groups and runs the aggregates once per group.
• HAVING is a filter that runs on groups, after the aggregates are computed.
• FILTER is a condition attached to one aggregate, so it counts or sums only some rows.
• A grouping set is one particular way of grouping. GROUPING SETS, ROLLUP and CUBE compute several groupings in one query.
• date_trunc cuts a date or timestamp down to a unit (day, week, month, year), which makes it a good group key.
• A report here just means a query that summarises data.

Aggregates without GROUP BY

With no GROUP BY, all rows form one group, so you get exactly one result row.

SELECT count(*) AS products, sum(price) AS sum_price, avg(price) AS avg_price,       min(price) AS cheapest, max(price) AS dearestFROM products;

 products | sum_price |      avg_price      | cheapest | dearest ----------+-----------+---------------------+----------+---------        6 |    143.60 | 23.9333333333333333 |     1.20 |   59.00(1 row)

avg of a numeric column gives a long decimal. Use round(avg(price), 2) for display. min and max also work on text and dates. sum of the whole order lines (quantity * unit_price) gives total revenue:

SELECT sum(quantity * unit_price) AS total_revenue FROM order_items;

 total_revenue ---------------        277.80

COUNT(*) versus COUNT(column), and NULL

This is the most asked aggregate question. Our customers table has 6 rows, and Fay has no city:

SELECT count(*) AS all_rows, count(city) AS cities_known,       count(DISTINCT city) AS distinct_citiesFROM customers;

 all_rows | cities_known | distinct_cities ----------+--------------+-----------------        6 |            5 |               3

• count(*) counts rows. It never looks at values.
• count(city) counts rows where city is not NULL.
• count(DISTINCT city) counts different non-NULL values: Pune, London, Lagos.

The same rule holds for the other aggregates: they ignore NULL. sum, avg, min and max skip NULL values, so avg is the sum of the non-NULL values divided by their count, not by the row count. If all values are NULL, or there are no rows, sum and avg return NULL, not 0. But count returns 0:

SELECT sum(quantity) FROM order_items WHERE order_id = 999;SELECT count(*) FROM order_items WHERE order_id = 999;SELECT coalesce(sum(quantity), 0) FROM order_items WHERE order_id = 999;

 sum -----    (1 row) count -------     0(1 row) coalesce ----------        0(1 row)

Wrap a sum in coalesce(..., 0) when you want a number in a report.

GROUP BY

"How many orders in each status?"

SELECT status, count(*) AS ordersFROM ordersGROUP BY statusORDER BY status;

  status   | orders -----------+-------- cancelled |      1 new       |      1 paid      |      3 shipped   |      2(4 rows)

How to picture it: the database sorts the 7 order rows into four piles by status, then runs count(*) on each pile and writes one row per pile.

The rule: every column in SELECT must be either in GROUP BY or inside an aggregate function. Otherwise the database does not know which value of the pile to show:

SELECT customer_id, status FROM orders GROUP BY customer_id;

ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function

Customer 1 has three orders with two different statuses. There is no single status to print. (One exception: if you group by a table's primary key, PostgreSQL lets you select other columns of that same table, because the key decides them.)

You can group by several columns, by an expression, or by position (GROUP BY 1), and GROUP BY accepts an output alias (a PostgreSQL extension; HAVING does not, as you will see):

SELECT customer_id AS cust, count(*) AS nFROM orders GROUP BY cust ORDER BY n DESC, cust;

 cust | n ------+---    1 | 3    3 | 2    2 | 1    4 | 1

Group by a joined table, and keep the empty groups

Customer names live in customers, so join first, then group. To include customers with no orders (Eli, Fay), use a LEFT JOIN, and count a column from the right table:

SELECT c.name, count(o.id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id, c.nameORDER BY c.id;

 name | orders ------+-------- Asha |      3 Ben  |      1 Chen |      2 Dara |      1 Eli  |      0 Fay  |      0(6 rows)

count(o.id) skips the NULL that the left join made for Eli and Fay, so they get 0. With count(*) they would get 1, because the row exists even though it holds no order. Group by c.id as well as c.name, because two customers could share a name.

WHERE versus HAVING

• WHERE filters rows, before grouping. It cannot use aggregates.
• HAVING filters groups, after grouping. It is where you use aggregates.

SELECT status, count(*) FROM orders WHERE count(*) > 1 GROUP BY status;

ERROR:  aggregate functions are not allowed in WHERE

The row filter does not know the group size yet. Use HAVING for that. Customers with at least two orders:

SELECT customer_id, count(*) AS nFROM ordersGROUP BY customer_idHAVING count(*) >= 2ORDER BY customer_id;

 customer_id | n -------------+---           1 | 3           3 | 2

You can use both in one query. Here WHERE first removes cancelled orders, then HAVING keeps customers with at least two of the rest:

SELECT customer_id, count(*) AS nFROM ordersWHERE status <> 'cancelled'GROUP BY customer_idHAVING count(*) >= 2;

 customer_id | n -------------+---           1 | 3

Chen dropped out: of Chen's two orders, one was cancelled. Rule of thumb: if a condition does not need an aggregate, put it in WHERE. It is cleaner and lets the database throw rows away early. Also, HAVING cannot use an output alias: HAVING n >= 2 fails with column "n" does not exist. Repeat the expression.

FILTER: many counts in one pass

Often you want several conditional counts side by side. FILTER (WHERE ...) applies a condition to one aggregate:

SELECT customer_id,       count(*)                                  AS orders,       count(*) FILTER (WHERE status = 'paid')      AS paid,       count(*) FILTER (WHERE status = 'cancelled') AS cancelledFROM ordersGROUP BY customer_idORDER BY customer_id;

 customer_id | orders | paid | cancelled -------------+--------+------+-----------           1 |      3 |    2 |         0           2 |      1 |    1 |         0           3 |      2 |    0 |         1           4 |      1 |    0 |         0

One pass over the table gives three numbers per customer. FILTER is standard SQL and PostgreSQL has had it since 9.4. The older way, sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), does the same and works everywhere. FILTER also gives a share: count(*) FILTER (WHERE status = 'paid')::numeric / count(*) gave 0.4285... (3 of 7).

DISTINCT inside aggregates

count(DISTINCT x) counts different values. It answers "how many different customers ordered" and not "how many orders":

SELECT count(DISTINCT customer_id) AS buyers, count(customer_id) AS orders FROM orders;

 buyers | orders --------+--------      4 |      7

It also fixes the join fan-out problem from lesson 1. If you join customers to orders to order lines, count(*) counts lines, not orders:

SELECT c.name, count(*) AS rows_joined, count(DISTINCT o.id) AS ordersFROM customers cJOIN orders o ON o.customer_id = c.idJOIN order_items oi ON oi.order_id = o.idGROUP BY c.name ORDER BY c.name;

 name | rows_joined | orders ------+-------------+-------- Asha |           6 |      3 Ben  |           2 |      1 Chen |           3 |      2 Dara |           1 |      1

Other handy aggregates: string_agg(name, ', ' ORDER BY name) joins text (the Pune customers gave Asha, Chen), array_agg(id ORDER BY id) collects values into an array ({1,3}), and bool_or, bool_and work on booleans.

Reports: revenue per category and per month

Revenue per category joins the order lines to products:

SELECT p.category, sum(oi.quantity * oi.unit_price) AS revenueFROM order_items oiJOIN products p ON p.id = oi.product_idGROUP BY p.categoryORDER BY revenue DESC;

  category  | revenue ------------+--------- audio      |  118.00 bags       |   90.00 stationery |   44.90 home       |   24.90

(Gift card sold nothing, so its category does not appear. An inner join drops empty groups.) For time reports use date_trunc, which rounds a timestamp down to the start of a unit:

SELECT date_trunc('week', timestamp '2025-03-15 20:10') AS week_start,       date_trunc('month', timestamp '2025-03-15 20:10') AS month_start;

     week_start      |     month_start     ---------------------+--------------------- 2025-03-10 00:00:00 | 2025-03-01 00:00:00

(Weeks start on Monday, and 10 March 2025 is a Monday.) Group by it for a monthly report. Because each order has several lines, count the order id with DISTINCT:

SELECT date_trunc('month', o.ordered_at)::date AS month,       count(DISTINCT o.id)                    AS orders,       sum(oi.quantity * oi.unit_price)        AS revenueFROM orders oJOIN order_items oi ON oi.order_id = o.idGROUP BY 1ORDER BY 1;

   month    | orders | revenue ------------+--------+--------- 2025-01-01 |      1 |   13.00 2025-02-01 |      3 |  132.40 2025-03-01 |      3 |  132.40

GROUP BY 1 means "the first column of the select list". ::date casts the month to a plain date for cleaner output. A month with no orders is simply missing. To show it as zero, generate the months and left join:

SELECT gs::date AS month, coalesce(x.n, 0) AS ordersFROM generate_series('2025-01-01'::date, '2025-04-01'::date, interval '1 month') gsLEFT JOIN (SELECT date_trunc('month', ordered_at) AS m, count(*) AS n           FROM orders GROUP BY 1) x ON x.m = gsORDER BY 1;

   month    | orders ------------+-------- 2025-01-01 |      1 2025-02-01 |      3 2025-03-01 |      3 2025-04-01 |      0

GROUPING SETS and ROLLUP, briefly

A report often needs details and subtotals and a grand total. Instead of three queries and a UNION, ask for several groupings at once. ROLLUP (a, b) gives the groups (a, b), (a) and (), i.e. detail, subtotal per a, and the grand total:

SELECT p.category, p.name, sum(oi.quantity) AS unitsFROM order_items oiJOIN products p ON p.id = oi.product_idGROUP BY ROLLUP (p.category, p.name)ORDER BY p.category NULLS LAST, p.name NULLS LAST;

  category  |    name    | units ------------+------------+------- audio      | Headphones |     2 audio      |            |     2 bags       | Backpack   |     2 bags       |            |     2 home       | Desk lamp  |     1 home       |            |     1 stationery | Notebook   |     7 stationery | Pen        |    17 stationery |            |    24            |            |    29(10 rows)

The row stationery | (blank) | 24 is the subtotal for the category, and the last row is the grand total (29 units). The blanks are NULLs made by the rollup. The same result comes from GROUP BY GROUPING SETS ((p.category, p.name), (p.category), ()), which lets you pick any combinations. CUBE (a, b) gives all four combinations. If your data has real NULLs in a group column, use the grouping() function to tell "subtotal NULL" from "real NULL": grouping(p.category, p.name) returned 0 on detail rows, 1 on a category subtotal and 3 on the grand total.

Order of evaluation of a SELECT

You write SELECT first, but the database does not run it first. The logical order is:

1. FROM / JOIN      build the rows (apply ON)2. WHERE            keep rows3. GROUP BY         make groups4. aggregates       compute count, sum, ...5. HAVING           keep groups6. SELECT           compute the output columns (and their aliases)7. DISTINCT         remove duplicate output rows8. ORDER BY         sort9. LIMIT / OFFSET   cut

(This is the logical order. The planner may run things in another order if the result is the same.) It explains most of the errors in this lesson:

• WHERE count(*) > 1 fails because aggregates (step 4) do not exist yet at WHERE (step 2).
• HAVING n >= 2 fails because the alias n is created in step 6, after HAVING.
• ORDER BY n DESC works because sorting (step 8) happens after SELECT. GROUP BY cust works because PostgreSQL makes a special exception for output aliases there.
• LIMIT cuts after sorting, which is why "top 3" needs ORDER BY first.

Common mistakes

1. count(*) after a LEFT JOIN when you mean "count the matches".
2. Counting or summing after a join that multiplied rows. Use count(DISTINCT o.id) or aggregate first.
3. Using HAVING for a plain row filter that belongs in WHERE.
4. Forgetting that sum and avg return NULL for no rows.
5. Grouping by a name instead of a key, so two people with the same name are merged.
6. avg of integers expecting a rounded integer. avg returns a numeric; cast or round as you need.

Try it

1. Total quantity sold per product, with the product name, biggest first.
2. How many customers have a city, how many do not, and how many different cities are there? (One query.)
3. Customers who spent more than 50 in total on non-cancelled orders.
4. For each order status, the number of orders and the average order total. (Hint: total per order first, in a subquery.)
5. Number of orders per month, with a column for how many were paid.

Answers

1. SELECT p.name, sum(oi.quantity) AS units FROM order_items oi JOIN products p ON p.id = oi.product_id GROUP BY p.name ORDER BY units DESC; gives Pen 17, Notebook 7, Backpack 2, Headphones 2, Desk lamp 1.
2. SELECT count(city) AS with_city, count(*) - count(city) AS without_city, count(DISTINCT city) AS cities FROM customers; gives 5, 1, 3.
3. Same shape as the HAVING example above, with > 50: it gives customer 1 with 178.40 (Asha). Customers 2 and 3 have 48.50 and 22.50.
4. SELECT o.status, count(*) AS orders, round(avg(t.total), 2) AS avg_total FROM orders o JOIN (SELECT order_id, sum(quantity * unit_price) AS total FROM order_items GROUP BY order_id) t ON t.order_id = o.id GROUP BY o.status; Summing in the subquery first avoids the fan-out.
5. SELECT date_trunc('month', ordered_at)::date AS month, count(*) AS orders, count(*) FILTER (WHERE status = 'paid') AS paid FROM orders GROUP BY 1 ORDER BY 1; gives January 1 and 1, February 3 and 1, March 3 and 1.

Interview questions

Key takeaways

• Aggregates turn many rows into one value. They ignore NULL, except count(*).
• GROUP BY makes groups. Every selected column must be grouped or aggregated.
• WHERE filters rows before grouping; HAVING filters groups after.
• After a LEFT JOIN, count a right-table column, not *. After a one-to-many join, beware of double counting.
• FILTER gives conditional aggregates in one pass; date_trunc makes time groups; ROLLUP adds subtotals.
• Know the evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

What's next

Lesson 3: Subqueries, CTEs and Window Functions. Aggregates collapse rows. Window functions keep the rows and still compute things across them, which is how you build rankings, running totals and "top N per group". For the Prisma and SQL interview questions, see Full-Stack Q&A, Lesson 08.