Aggregations and GROUP BY

An aggregate turns many rows into one value; GROUP BY does that once per group. Every example uses one sales table.

-- setup.sql
CREATE TABLE sales (
    id        SERIAL PRIMARY KEY,
    region    TEXT NOT NULL,
    rep       TEXT NOT NULL,
    amount    NUMERIC(8, 2) NOT NULL,
    discount  NUMERIC(4, 2)          -- NULL = no discount recorded
);

INSERT INTO sales (region, rep, amount, discount) VALUES
    ('North', 'Ana', 1200.00, 0.10), ('North', 'Ana',  300.00, NULL),
    ('North', 'Ben',  800.00, 0.05), ('South', 'Cai', 1500.00, 0.20),
    ('South', 'Cai',  200.00, NULL), ('South', 'Dee',  450.00, NULL),
    ('West',  'Eve',  950.00, 0.15);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 7

The Core Aggregates

With no GROUP BY, the whole table is one group.

-- core.sql
SELECT count(*)         AS rows,
       count(discount)  AS with_discount,
       sum(amount)      AS total,
       round(avg(amount), 2) AS avg_amount,
       min(amount)      AS smallest,
       max(amount)      AS largest
FROM sales;

$ psql -d sqldemo -f core.sql
 rows | with_discount |  total  | avg_amount | smallest | largest 
------+---------------+---------+------------+----------+---------
    7 |             4 | 5400.00 |     771.43 |   200.00 | 1500.00
(1 row)

count(*) counts rows (7), but count(discount) counts only non-NULL discounts (4). That gap is the first sign that aggregates treat NULL specially.

AVG Skips NULLs

What is the average discount across all sales?

-- avg_null.sql
SELECT round(avg(discount), 4)              AS avg_ignoring_nulls,
       round(avg(COALESCE(discount, 0)), 4) AS avg_nulls_as_zero
FROM sales;

$ psql -d sqldemo -f avg_null.sql
 avg_ignoring_nulls | avg_nulls_as_zero 
--------------------+-------------------
             0.1250 |            0.0714
(1 row)

avg(discount) divides by 4, the rows that have a discount, not by 7. Both numbers are “correct”; they answer different questions. If a missing discount means “no discount”, only the COALESCE version is right.

GROUP BY

GROUP BY produces one output row per distinct value of the grouping columns.

-- group_by.sql
SELECT region, count(*) AS sales, sum(amount) AS total
FROM sales
GROUP BY region
ORDER BY total DESC;

$ psql -d sqldemo -f group_by.sql
 region | sales |  total  
--------+-------+---------
 North  |     3 | 2300.00
 South  |     3 | 2150.00
 West   |     1 |  950.00
(3 rows)

Each region collapses to a single row. Every column in the select list must either appear in GROUP BY or be inside an aggregate, otherwise the database cannot know which of the group’s values to show.

-- group_by_error.sql
SELECT region, rep, sum(amount)
FROM sales
GROUP BY region;

$ psql -d sqldemo -f group_by_error.sql
psql:group_by_error.sql:4: ERROR:  column "sales.rep" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT region, rep, sum(amount)
                       ^

North has two reps, so “the rep for North” has no single answer. Add rep to GROUP BY to get one row per region and rep, or aggregate it, for example with string_agg(rep, ', ').

HAVING vs WHERE

WHERE filters rows before grouping; HAVING filters groups after.

-- having.sql
SELECT rep, count(*) AS big_sales, sum(amount) AS total
FROM sales
WHERE amount >= 300
GROUP BY rep
HAVING sum(amount) > 1000
ORDER BY total DESC;

$ psql -d sqldemo -f having.sql
 rep | big_sales |  total  
-----+-----------+---------
 Ana |         2 | 1500.00
 Cai |         1 | 1500.00
(2 rows)

WHERE threw away Cai’s 200 sale before grouping (the 300 sale stays because of >=), then HAVING kept only reps whose remaining total beats 1000. That is why Cai’s total is 1500, not 1700. Writing WHERE sum(amount) > 1000 is an error, because no groups exist yet when WHERE runs.

COUNT(DISTINCT) and FILTER

count(DISTINCT col) counts unique values; FILTER restricts one aggregate without touching the others.

-- filter.sql
SELECT region,
       count(DISTINCT rep)                         AS reps,
       count(*) FILTER (WHERE amount >= 500)       AS large,
       count(*) FILTER (WHERE discount IS NULL)    AS undiscounted
FROM sales
GROUP BY region
ORDER BY region;

$ psql -d sqldemo -f filter.sql
 region | reps | large | undiscounted 
--------+------+-------+--------------
 North  |    2 |     2 |            1
 South  |    2 |     1 |            2
 West   |    1 |     1 |            0
(3 rows)

FILTER is PostgreSQL’s clean way to pivot counts into columns. In databases without it, the same result comes from sum(CASE WHEN amount >= 500 THEN 1 ELSE 0 END).

ROLLUP: Subtotals and a Grand Total

ROLLUP adds a subtotal row per region and one grand-total row.

-- rollup.sql
SELECT region, rep, sum(amount) AS total, GROUPING(rep) AS is_subtotal
FROM sales
GROUP BY ROLLUP (region, rep)
ORDER BY region NULLS LAST, rep NULLS LAST;

$ psql -d sqldemo -f rollup.sql
 region | rep |  total  | is_subtotal 
--------+-----+---------+-------------
 North  | Ana | 1500.00 |           0
 North  | Ben |  800.00 |           0
 North  |     | 2300.00 |           1
 South  | Cai | 1700.00 |           0
 South  | Dee |  450.00 |           0
 South  |     | 2150.00 |           1
 West   | Eve |  950.00 |           0
 West   |     |  950.00 |           1
        |     | 5400.00 |           1
(9 rows)

Rows with an empty rep are subtotals for their region, and the row with both columns empty is the grand total. GROUPING(rep) returns 1 on those rows, which is how you tell a subtotal apart from a real NULL rep in the data.

The Join Fan-Out Trap

Aggregating after a join can count the same value several times. Each order below has one shipping fee and several line items.

-- fanout.sql
CREATE TABLE orders (id INT PRIMARY KEY, shipping NUMERIC(6, 2));
CREATE TABLE order_items (order_id INT REFERENCES orders(id), price NUMERIC(6, 2));
INSERT INTO orders VALUES (1, 5.00), (2, 8.00);
INSERT INTO order_items VALUES (1, 10.00), (1, 20.00), (1, 30.00), (2, 40.00);

SELECT sum(o.shipping) AS shipping_total, sum(i.price) AS items_total
FROM orders o JOIN order_items i ON i.order_id = o.id;

$ psql -d sqldemo -f fanout.sql
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 4
 shipping_total | items_total 
----------------+-------------
          23.00 |      100.00
(1 row)

The real shipping total is 13.00, but the query reports 23.00. Order 1 has three items, so the join repeats its 5.00 fee three times. Aggregate at the right grain first.

-- fanout_fixed.sql
SELECT (SELECT sum(shipping) FROM orders)     AS shipping_total,
       (SELECT sum(price) FROM order_items)   AS items_total;

$ psql -d sqldemo -f fanout_fixed.sql
 shipping_total | items_total 
----------------+-------------
          13.00 |      100.00
(1 row)

Each total now comes from the table that owns the value. The same bug hides in dashboards whenever a per-order number meets a per-line join.

Practice

1. Show each rep’s number of sales and average sale amount, rounded to 2 decimals.

-- practice_rep_avg.sql
SELECT rep, count(*) AS sales, round(avg(amount), 2) AS avg_amount
FROM sales GROUP BY rep ORDER BY rep;

$ psql -d sqldemo -f practice_rep_avg.sql
 rep | sales | avg_amount 
-----+-------+------------
 Ana |     2 |     750.00
 Ben |     1 |     800.00
 Cai |     2 |     850.00
 Dee |     1 |     450.00
 Eve |     1 |     950.00
(5 rows)

2. List regions with more than two sales.

-- practice_busy_regions.sql
SELECT region, count(*) AS sales
FROM sales GROUP BY region HAVING count(*) > 2 ORDER BY region;

$ psql -d sqldemo -f practice_busy_regions.sql
 region | sales 
--------+-------
 North  |     3
 South  |     3
(2 rows)

3. Per region, compute the total amount of discounted sales and of undiscounted sales as two columns.

-- practice_pivot.sql
SELECT region,
       COALESCE(sum(amount) FILTER (WHERE discount IS NOT NULL), 0) AS discounted,
       COALESCE(sum(amount) FILTER (WHERE discount IS NULL), 0)     AS full_price
FROM sales GROUP BY region ORDER BY region;

$ psql -d sqldemo -f practice_pivot.sql
 region | discounted | full_price 
--------+------------+------------
 North  |    2000.00 |     300.00
 South  |    1500.00 |     650.00
 West   |     950.00 |          0
(3 rows)

4. Rewrite fanout.sql so it returns the correct totals per order in one query.

-- practice_fanout_per_order.sql
SELECT o.id, o.shipping, i.items_total
FROM orders o
JOIN (SELECT order_id, sum(price) AS items_total
      FROM order_items GROUP BY order_id) i ON i.order_id = o.id
ORDER BY o.id;

$ psql -d sqldemo -f practice_fanout_per_order.sql
 id | shipping | items_total 
----+----------+-------------
  1 |     5.00 |       60.00
  2 |     8.00 |       40.00
(2 rows)