Aggregation

So far every query has returned one result row per source row (or per joined pair). Business questions are usually about summaries: how many orders came in last month, what each customer has spent, which genre earns the most. Aggregate functions collapse many rows into one value, and GROUP BY decides which rows get collapsed together. This lesson covers both, plus HAVING, the FILTER clause, and the subtle ways NULLs and joins distort totals.

#Aggregate functions

An aggregate function takes a whole set of rows and returns a single value. Without GROUP BY, the set is every row that passed the WHERE clause:

SELECT min(price)            AS cheapest,
       max(price)            AS priciest,
       round(avg(price), 2)  AS avg_price,
       sum(stock)            AS copies
FROM books;

 cheapest | priciest | avg_price | copies
----------+----------+-----------+--------
    10.99 |    18.50 |     14.07 |     78
(1 row)

The core aggregates:

#The three kinds of count

The difference between the count variants is the first thing to get right:

SELECT count(*)             AS customers,
       count(city)          AS with_city,
       count(DISTINCT city) AS cities
FROM customers;

 customers | with_city | cities
-----------+-----------+--------
         6 |         4 |      2
(1 row)

Six customers, four of whom have a city, spread across two distinct cities (Leeds and London). count(city) skips the two NULLs.

#NULLs and empty sets

Because aggregates skip NULLs, avg divides by the number of non-null values, which may not be what you mean:

SELECT avg(x)              AS avg_ignoring_null,
       avg(coalesce(x, 0)) AS avg_null_as_zero
FROM (VALUES (10), (20), (NULL)) AS t (x);

  avg_ignoring_null  |  avg_null_as_zero
---------------------+---------------------
 15.0000000000000000 | 10.0000000000000000
(1 row)

Neither is wrong — "average rating among people who rated" versus "average with no rating counted as zero" are different questions. Decide which one you are asking.

When no rows match, count returns 0 but every other aggregate returns NULL:

SELECT count(*) AS n, sum(price) AS total, max(price) AS priciest
FROM books
WHERE genre = 'horror';

 n | total  | priciest
---+--------+----------
 0 | (null) |   (null)
(1 row)

If your application expects a number, write coalesce(sum(price), 0).

#GROUP BY: one result row per group

GROUP BY splits the rows into groups that share the same value of the grouping expressions, then computes the aggregates once per group:

SELECT genre,
       count(*)             AS books,
       round(avg(price), 2) AS avg_price,
       sum(stock)           AS copies
FROM books
GROUP BY genre
ORDER BY genre;

  genre   | books | avg_price | copies
----------+-------+-----------+--------
 fantasy  |     4 |     11.62 |     48
 literary |     2 |     17.75 |      8
 sci-fi   |     4 |     14.69 |     22
(3 rows)

Picture it as sorting the ten books into three piles by genre, then measuring each pile. You can group by several columns (GROUP BY genre, author_id gives one row per combination) or by an expression, such as a month:

SELECT date_trunc('month', o.ordered_on)::date AS month,
       count(DISTINCT o.order_id)              AS orders,
       sum(oi.quantity * oi.unit_price)        AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
ORDER BY month;

   month    | orders | revenue
------------+--------+---------
 2025-01-01 |      2 |   49.97
 2025-02-01 |      2 |   67.74
 2025-03-01 |      5 |  141.95
(3 rows)

date_trunc('month', ...) rounds a date down to the first of its month. PostgreSQL lets GROUP BY refer to a select-list alias like month as a convenience; the SQL standard does not, so some databases require you to repeat the expression.

#The grouping rule

Once you group, each result row stands for a whole group, so every item in the select list must have a single value per group. It must either be a grouping expression or be inside an aggregate:

SELECT genre, title, count(*) FROM books GROUP BY genre;

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

Which of the four fantasy titles would PostgreSQL show? There is no answer, so it refuses. (MySQL in some modes picks one arbitrarily, which hides bugs.)

There is one useful exception. If you group by a table's primary key, PostgreSQL knows every other column of that table has one value per group, so you may select them freely:

SELECT c.customer_id, c.name, sum(oi.quantity * oi.unit_price) AS revenue
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id
ORDER BY revenue DESC;

 customer_id |    name     | revenue
-------------+-------------+---------
           1 | Ada Park    |   74.96
           2 | Ben Okafor  |   73.48
           4 | Dev Raman   |   72.25
           5 | Elena Rossi |   38.97
(4 rows)

#HAVING: filtering groups

WHERE filters rows before grouping, so it cannot use aggregates. HAVING filters groups after aggregation. Customers with at least two orders:

SELECT c.name, count(*) AS orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING count(*) >= 2
ORDER BY c.name;

    name    | orders
------------+--------
 Ada Park   |      3
 Ben Okafor |      3
 Dev Raman  |      2
(3 rows)

The complete logical evaluation order of a query is now:

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Put a condition in WHERE whenever it is about individual rows (status <> 'cancelled'), and in HAVING only when it needs an aggregate (sum(...) > 50). Filtering early means fewer rows to group.

#Conditional aggregates with FILTER

Often you want several differently filtered counts side by side. The FILTER clause attaches a condition to one aggregate:

SELECT count(*)                                   AS all_orders,
       count(*) FILTER (WHERE status = 'paid')      AS paid,
       count(*) FILTER (WHERE status = 'shipped')   AS shipped,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

 all_orders | paid | shipped | cancelled
------------+------+---------+-----------
         10 |    4 |       5 |         1
(1 row)

This is clearer than the portable alternative, sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), which you will see in older code and other databases.

For yes/no questions about a group, bool_or is true if the condition holds for any row and bool_and only if it holds for every row:

SELECT genre,
       count(*)                  AS books,
       bool_or(stock = 0)        AS any_sold_out,
       bool_and(published < 2000) AS all_pre_2000
FROM books
GROUP BY genre
ORDER BY genre;

  genre   | books | any_sold_out | all_pre_2000
----------+-------+--------------+--------------
 fantasy  |     4 | f            | f
 literary |     2 | t            | f
 sci-fi   |     4 | t            | t
(3 rows)

Incidentally, a GROUP BY with no aggregates, such as SELECT genre FROM books GROUP BY genre, returns the same rows as SELECT DISTINCT genre FROM books. Use DISTINCT when you only want unique rows and GROUP BY when you want to compute something per group; mixing them in one query is usually a sign the grouping is wrong.

#Collecting values and subtotals

string_agg and array_agg gather a group's values, and accept their own ORDER BY:

SELECT a.name, string_agg(b.title, ', ' ORDER BY b.published) AS titles
FROM authors AS a
JOIN books AS b ON b.author_id = a.author_id
GROUP BY a.author_id
ORDER BY a.name;

       name        |                              titles
-------------------+-------------------------------------------------------------------
 Haruki Murakami   | Norwegian Wood, Kafka on the Shore
 Octavia E. Butler | Kindred, Parable of the Sower
 Terry Pratchett   | Guards! Guards!, Small Gods, Going Postal
 Ursula K. Le Guin | A Wizard of Earthsea, The Left Hand of Darkness, The Dispossessed
(4 rows)

For reports with subtotals, GROUP BY ROLLUP (genre) adds a grand-total row in which the rolled-up column is NULL:

SELECT genre, count(*) AS books, sum(stock) AS copies
FROM books
GROUP BY ROLLUP (genre)
ORDER BY genre;

  genre   | books | copies
----------+-------+--------
 fantasy  |     4 |     48
 literary |     2 |      8
 sci-fi   |     4 |     22
 (null)   |    10 |     78
(4 rows)

ROLLUP (genre, author_id) produces per-author rows, per-genre subtotals and a grand total in one pass. GROUPING SETS and CUBE offer finer control over which combinations appear.

#Pitfalls: joins that inflate counts

#Counting the wrong rows after a join

A join multiplies the "one" side once per matching row on the "many" side. Count orders per customer after joining to order_items, and count(*) counts order lines instead:

SELECT c.name,
       count(*)                   AS rows_counted,
       count(DISTINCT o.order_id) AS orders
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
GROUP BY c.customer_id
ORDER BY c.name;

     name     | rows_counted | orders
--------------+--------------+--------
 Ada Park     |            5 |      3
 Ben Okafor   |            5 |      3
 Chloe Martin |            1 |      1
 Dev Raman    |            4 |      2
 Elena Rossi  |            1 |      1
(5 rows)

count(DISTINCT ...) fixes counts, but sums are harder: if orders had a shipping_fee column, sum(o.shipping_fee) here would add each fee once per line. The cure is to aggregate at the right level first — for example, total each order's lines in a subquery, then join that to customers — a technique you will learn in the next lesson.

#count(*) with a LEFT JOIN

To include customers with no orders you need a LEFT JOIN, but count(*) counts the NULL-padded row as one:

SELECT c.name, count(*) AS count_star, count(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.name;

     name     | count_star | orders
--------------+------------+--------
 Ada Park     |          3 |      3
 Ben Okafor   |          3 |      3
 Chloe Martin |          1 |      1
 Dev Raman    |          2 |      2
 Elena Rossi  |          1 |      1
 Farid Haddad |          1 |      0
(6 rows)

#Practice

1. For each genre, show the number of copies sold and the revenue from non-cancelled orders. Only show genres with more than £50 of revenue, highest revenue first.
2. For every customer, show the number of non-cancelled orders and their total spend. Customers with no such orders must show 0 for both. Sort by spend (highest first), then name.

SELECT b.genre,
       sum(oi.quantity)                 AS copies_sold,
       sum(oi.quantity * oi.unit_price) AS revenue
FROM books AS b
JOIN order_items AS oi ON oi.book_id  = b.book_id
JOIN orders      AS o  ON o.order_id  = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY b.genre
HAVING sum(oi.quantity * oi.unit_price) > 50
ORDER BY revenue DESC;

  genre  | copies_sold | revenue
---------+-------------+---------
 fantasy |          12 |  139.41
 sci-fi  |           6 |   86.25
(2 rows)

SELECT c.name,
       count(DISTINCT o.order_id)                     AS orders,
       coalesce(sum(oi.quantity * oi.unit_price), 0)  AS spent
FROM customers AS c
LEFT JOIN orders      AS o  ON o.customer_id = c.customer_id
                           AND o.status <> 'cancelled'
LEFT JOIN order_items AS oi ON oi.order_id   = o.order_id
GROUP BY c.customer_id
ORDER BY spent DESC, c.name;

     name     | orders | spent
--------------+--------+-------
 Ada Park     |      3 | 74.96
 Ben Okafor   |      3 | 73.48
 Dev Raman    |      2 | 72.25
 Elena Rossi  |      1 | 38.97
 Chloe Martin |      0 |     0
 Farid Haddad |      0 |     0
(6 rows)

#Key takeaways

• Aggregates collapse many rows into one value; all except count(*) ignore NULLs, and all except count return NULL over zero rows.
• GROUP BY produces one row per group; every selected column must be grouped or aggregated, though grouping by a primary key lets you select that table's other columns.
• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• FILTER (WHERE ...) computes several conditional aggregates in one pass.
• Joins multiply rows: use count(DISTINCT key), count a right-hand key after a LEFT JOIN, and aggregate at the correct level before summing.