Aggregation and Grouping

What is it? #

Aggregation turns many rows into one number. "How many orders?" "What is the total revenue?" "What is the average basket size?"

There are five functions doing nearly all the work: count, sum, avg, min and max.

On their own they collapse the whole table into a single row. Add GROUP BY and they collapse each group into a row instead — revenue per city, orders per month, average spend per customer.

HAVING then filters those groups, which is the piece people mix up with WHERE. The distinction is simple once stated: WHERE chooses which rows go into the groups, HAVING chooses which groups survive.

Think of it like this #

You have a pile of receipts.

count is counting the receipts. sum is adding the amounts. avg is the total divided by the count.

GROUP BY city is sorting the receipts into one pile per city first, then counting and adding each pile separately.

WHERE is throwing out receipts before you sort them — "ignore anything cancelled". HAVING is throwing out whole piles after they are totalled — "ignore any city below 500". You cannot do the second one before totalling, because the total does not exist yet.

Simple example #

A shop wants a monthly report: how many orders, total revenue, average order value, and the largest single order — broken down by city, but only counting completed orders, and only showing cities that actually matter.

That is one query using every idea in this lesson.

Code #

-- ---------- The five functions ----------

SELECT
    count(*)              AS total_rows,      -- counts ROWS (NULLs included)
    count(city)           AS rows_with_city,  -- counts NON-NULL values only
    count(DISTINCT city)  AS distinct_cities, -- counts unique non-NULL values
    sum(total)            AS revenue,
    avg(total)            AS average_order,
    min(total)            AS smallest,
    max(total)            AS largest
FROM orders;

-- Without GROUP BY, the whole table collapses into exactly one row.

-- ---------- GROUP BY: one row per group ----------

SELECT city,
       count(*)   AS orders,
       sum(total) AS revenue
FROM orders
GROUP BY city
ORDER BY revenue DESC NULLS LAST;

-- Pune   | 12 | 5400.00
-- Kochi  |  8 | 3100.00
-- NULL   |  3 |  900.00     <-- all unknown-city rows form ONE group

-- ---------- WHERE vs HAVING: the distinction that matters ----------

SELECT city,
       count(*)   AS orders,
       sum(total) AS revenue
FROM orders
WHERE status = 'completed'      -- 1. filters ROWS, before grouping
GROUP BY city                   -- 2. forms the groups
HAVING sum(total) > 1000        -- 3. filters GROUPS, after totalling
ORDER BY revenue DESC;          -- 4. sorts the surviving groups

-- WHERE cannot use sum(), because sums do not exist yet when it runs.
-- HAVING can, because by then the groups are already totalled.

-- ---------- Grouping by more than one column ----------

SELECT date_trunc('month', placed_at) AS month,
       city,
       count(*)   AS orders,
       sum(total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY month, city            -- one row per (month, city) pair
ORDER BY month DESC, revenue DESC;

-- ---------- The rule every beginner hits ----------

-- WRONG:
SELECT city, customer, sum(total) FROM orders GROUP BY city;
-- ERROR: column "orders.customer" must appear in the GROUP BY clause
--        or be used in an aggregate function

-- Why: one city row collapses MANY customers. PostgreSQL has several possible
-- values for "customer" and no basis to choose one, so it refuses to guess.

-- Fix 1 — group by it too:
SELECT city, customer, sum(total) FROM orders GROUP BY city, customer;

-- Fix 2 — aggregate it:
SELECT city, count(DISTINCT customer) AS customers, sum(total)
FROM orders GROUP BY city;

-- Fix 3 — collect the values into a list:
SELECT city, string_agg(DISTINCT customer, ', ' ORDER BY customer) AS who
FROM orders GROUP BY city;

-- ---------- NULL handling in aggregates ----------

-- Aggregates SKIP NULLs. This matters more than it looks:
--   count(*)        counts every row
--   count(column)   counts rows where that column is not NULL
--   avg(column)     divides by the count of NON-NULL values, not by all rows
--   sum(nothing)    returns NULL, not 0

SELECT COALESCE(sum(total), 0) AS revenue   -- safe: never returns NULL
FROM orders
WHERE status = 'refunded';                  -- may match no rows at all

-- ---------- FILTER: different conditions in one pass ----------

SELECT
    count(*)                                        AS all_orders,
    count(*) FILTER (WHERE status = 'completed')    AS completed,
    count(*) FILTER (WHERE status = 'cancelled')    AS cancelled,
    sum(total) FILTER (WHERE status = 'completed')  AS real_revenue
FROM orders;

-- FILTER lets one scan of the table answer several questions at once,
-- instead of running three separate queries. It is a PostgreSQL strength.

-- ---------- A realistic monthly report ----------

SELECT
    date_trunc('month', o.placed_at)::date          AS month,
    COALESCE(o.city, 'Unknown')                     AS city,
    count(*)                                        AS orders,
    count(DISTINCT o.customer_id)                   AS customers,
    round(sum(o.total), 2)                          AS revenue,
    round(avg(o.total), 2)                          AS avg_order,
    max(o.total)                                    AS biggest_order
FROM orders o
WHERE o.status = 'completed'
  AND o.placed_at >= date_trunc('year', now())
GROUP BY month, COALESCE(o.city, 'Unknown')
HAVING count(*) >= 5                -- ignore cities with barely any activity
ORDER BY month DESC, revenue DESC;

How it works #

The order of operations explains every rule in this lesson. PostgreSQL applies WHERE first, then forms groups with GROUP BY, then computes the aggregates, then applies HAVING, then ORDER BY, then LIMIT.

Because WHERE runs before the aggregates are computed, it cannot reference them — WHERE sum(total) > 1000 is not just disallowed, it is meaningless at that point. HAVING runs after, so it can.

The "must appear in the GROUP BY clause" error is the one every beginner meets, and it is PostgreSQL protecting you. If you group by city, a single output row stands for many original rows. Those rows may have different customers. Asking for customer leaves PostgreSQL with several candidate values and no rule for picking, so it refuses rather than returning an arbitrary one. Some other databases do silently pick one, which is worse — you get a plausible-looking wrong answer. Every column in the select list must therefore be either grouped or aggregated.

Aggregates skip NULLs, and this has real consequences. count(*) counts rows; count(city) counts rows where city is known. avg divides by the number of non-NULL values, so an average over a column that is half empty is the average of the half that exists — which may or may not be what you intended. And sum() over zero rows returns NULL, not 0, which is why COALESCE(sum(...), 0) is such a common idiom in reports.

NULL in a GROUP BY column behaves differently from NULL in a comparison: all NULL values group together into one group. That is why COALESCE(city, 'Unknown') in both the select list and the GROUP BY produces a readable report.

FILTER is a genuinely useful PostgreSQL feature. Instead of running three queries to count completed, cancelled and total orders, one query with three FILTER clauses scans the table once and answers all three. On a large table that is three times less work.

Real-world use #

Aggregation queries are what reports and dashboards are made of, and they are also the queries most likely to become slow, because they usually have to read a great many rows to produce a small answer.

Two things help enormously. First, filter early: a WHERE clause on an indexed column reduces how many rows ever reach the grouping step. Second, be careful about aggregating after joining to a child table — the fan-out described in the joins lesson multiplies parent values and inflates sums. When both are needed, aggregate the child table in a subquery first, then join the result.

When an aggregate query becomes too slow to run live, the usual answers are a materialised view refreshed on a schedule, or a summary table updated as data arrives. Both are covered later in this track.

A quiet correctness issue worth naming: averages over columns containing NULL. "Average delivery time" computed over a column that is empty for undelivered orders is the average of delivered orders only. That is often the right number — but it should be a decision, not an accident.

Finally, round(sum(total), 2) on a numeric column keeps report output clean and exact. If the column were a floating-point type, no amount of rounding would fix the underlying approximation, which is the data-types lesson showing up again in practice.

Common mistakes #

• Putting an aggregate condition in WHERE instead of HAVING, which cannot work because the aggregate does not exist yet.
• Selecting a column that is neither grouped nor aggregated, then fighting the error instead of choosing one.
• Using count(*) where count(column) is meant, and counting rows whose value is actually NULL.
• Expecting sum() over no rows to return 0 when it returns NULL.
• Aggregating after a one-to-many join, so fan-out multiplies the parent values and inflates totals.

Practice #

Build a report on your orders table that shows, per month and per city: order count, number of distinct customers, revenue, average order value and the largest order — counting only completed orders, displaying unknown cities as "Unknown", and hiding any group with fewer than three orders. Then add three FILTER columns that count completed, cancelled and pending orders in the same single pass.