Advanced PostgreSQL Aggregations

Schema

One PostgreSQL schema, shop, with four tables plus one flattening view.

Setup

CREATE SCHEMA shop;
SET search_path = shop;

CREATE TABLE customers (
  id      int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  country text NOT NULL,
  segment text NOT NULL            -- 'retail' | 'enterprise'
);

CREATE TABLE products (
  id       int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name     text NOT NULL,
  category text NOT NULL,
  price    numeric(10,2) NOT NULL
);

CREATE TABLE orders (
  id          int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id),
  order_date  date NOT NULL,
  status      text NOT NULL,       -- pending | shipped | delivered | cancelled
  is_paid     boolean NOT NULL
);

CREATE TABLE order_items (
  id         int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id   int NOT NULL REFERENCES orders(id),
  product_id int NOT NULL REFERENCES products(id),
  quantity   int NOT NULL,
  unit_price numeric(10,2) NOT NULL
);

Dummy data:

INSERT INTO customers (id, name, country, segment) VALUES
(1,'Alice','US','retail'),   (2,'Bob','US','enterprise'),
(3,'Chitra','IN','retail'),  (4,'Dmitri','DE','enterprise'),
(5,'Emma','DE','retail'),    (6,'Farid','BD','retail');

INSERT INTO products (id, name, category, price) VALUES
(1,'Laptop','electronics',1200), (2,'Phone','electronics',800),
(3,'Headphones','electronics',150), (4,'Desk','furniture',300),
(5,'Chair','furniture',180),     (6,'Notebook','stationery',5);

INSERT INTO orders (id, customer_id, order_date, status, is_paid) VALUES
(1,1,'2026-01-10','delivered',true),  (2,1,'2026-02-14','delivered',true),
(3,2,'2026-02-20','delivered',true),  (4,2,'2026-03-05','shipped',true),
(5,3,'2026-03-12','delivered',true),  (6,3,'2026-04-01','cancelled',false),
(7,4,'2026-04-15','delivered',true),  (8,4,'2026-05-02','pending',false),
(9,5,'2026-05-20','delivered',true),  (10,6,'2026-06-11','shipped',true),
(11,2,'2026-06-18','delivered',true), (12,1,'2026-07-03','pending',false);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1,1,1,1200),(1,6,10,5),  (2,3,2,150),
(3,4,5,300),(3,5,5,180),  (4,2,3,800),
(5,2,1,800),(5,3,1,150),  (6,5,1,180),
(7,1,2,1200),(7,3,2,150), (8,4,1,300),
(9,5,2,180),(9,6,20,5),   (10,3,1,150),(10,6,5,5),
(11,1,1,1200),(11,2,1,800), (12,6,3,5);

A flattened view keeps the later queries short:

CREATE VIEW sales AS
SELECT o.id AS order_id, o.order_date, o.status, o.is_paid,
       c.id AS customer_id, c.name AS customer, c.country, c.segment,
       p.category, p.name AS product,
       oi.quantity, oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN orders    o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN products  p ON p.id = oi.product_id;

The basics, done properly.

COUNT

SELECT count(*)                         AS all_rows,     -- counts rows
       count(customer_id)               AS non_null,     -- skips NULLs
       count(DISTINCT customer_id)      AS buyers,       -- unique ids
       count(*) FILTER (WHERE is_paid)  AS paid_orders
FROM orders;

count(*) counts rows and count(col) counts non-NULLs. FILTER is cleaner and often faster than count(CASE WHEN ... END), and it works on every aggregate.

SUM and AVG

SELECT category,
       sum(line_total)                 AS revenue,
       round(avg(line_total), 2)       AS avg_line,
       coalesce(sum(line_total) FILTER (WHERE status = 'cancelled'), 0) AS lost
FROM sales
GROUP BY category;

sum() over zero rows returns NULL, not 0, so wrap it in coalesce. avg() ignores NULLs, which silently changes the denominator.

MIN / MAX

SELECT customer_id, min(order_date) AS first_order, max(order_date) AS last_order
FROM orders GROUP BY customer_id;

They work on any orderable type: dates, text, even arrays. For "the row with the max", use DISTINCT ON or a window function instead of a self-join.

Collecting values

STRING_AGG

SELECT order_id, string_agg(product, ', ' ORDER BY product) AS basket
FROM sales GROUP BY order_id;

ARRAY_AGG

SELECT customer, array_agg(DISTINCT category ORDER BY category) AS categories
FROM sales GROUP BY customer;

DISTINCT plus ORDER BY inside the aggregate gives deterministic output. Without ORDER BY, the order is whatever the executor feels like. array_agg keeps NULLs, while string_agg skips them.

JSON_AGG

SELECT order_id,
       jsonb_agg(jsonb_build_object('product', product, 'qty', quantity)
                 ORDER BY product) AS items
FROM sales GROUP BY order_id;

This is the standard way to build nested API payloads in a single query. Prefer jsonb_agg unless key order matters. jsonb_object_agg(k, v) builds a map instead of an array.

Boolean aggregates

SELECT customer,
       bool_and(is_paid)               AS all_paid,         -- "every"
       bool_or(status = 'cancelled')   AS ever_cancelled    -- "any"
FROM sales GROUP BY customer;

These are good for "all/any child rows satisfy X" checks without a NOT EXISTS subquery. NULLs are ignored, and an empty set returns NULL.

Statistical functions

SELECT category,
       round(stddev(line_total), 2)                              AS stddev_samp,
       round(variance(line_total), 2)                            AS var_samp,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY line_total)   AS median,
       percentile_cont(ARRAY[0.25, 0.75])
         WITHIN GROUP (ORDER BY line_total)                      AS q1_q3
FROM sales GROUP BY category;

• stddev and variance are the sample versions. Use stddev_pop and var_pop for the population.
• percentile_cont interpolates between values, while percentile_disc returns an actual value from the set.
• percentile_cont and percentile_disc are ordered-set aggregates, so they can't be used with OVER (...). For per-row percentiles, use ntile, percent_rank or cume_dist.
• mode() WITHIN GROUP (ORDER BY ...) gives the most frequent value.

Grouping

GROUP BY and HAVING

SELECT country,
       count(DISTINCT order_id) AS orders,
       sum(line_total)          AS revenue
FROM sales
WHERE status <> 'cancelled'          -- filters rows BEFORE grouping
GROUP BY country
HAVING sum(line_total) > 2000        -- filters groups AFTER aggregation
ORDER BY revenue DESC;

WHERE runs before aggregation and can use indexes, while HAVING runs after and sees aggregates. Push whatever you can into WHERE. You can group by ordinal (GROUP BY 1) or by expression, and any column that is functionally dependent on the primary key can be selected without being listed.

GROUPING SETS

Several GROUP BYs in one scan:

SELECT country, category, sum(line_total) AS revenue
FROM sales
GROUP BY GROUPING SETS ((country, category), (country), (category), ());

This replaces four queries and a UNION ALL with a single pass over the data. () is the grand total.

ROLLUP

Hierarchical subtotals. ROLLUP(a, b) is shorthand for GROUPING SETS ((a,b), (a), ()):

SELECT country, category, sum(line_total) AS revenue
FROM sales
GROUP BY ROLLUP (country, category);

CUBE

Every combination. CUBE(a, b) is GROUPING SETS ((a,b), (a), (b), ()):

SELECT country, category, sum(line_total) AS revenue
FROM sales
GROUP BY CUBE (country, category);

CUBE produces 2ⁿ grouping sets, so it blows up fast beyond 3 or 4 columns.

Telling NULLs from subtotals with GROUPING()

The rolled-up rows show NULL in the grouped column, which is indistinguishable from real NULL data. GROUPING() fixes that:

SELECT CASE WHEN GROUPING(country)  = 1 THEN 'ALL' ELSE country  END AS country,
       CASE WHEN GROUPING(category) = 1 THEN 'ALL' ELSE category END AS category,
       sum(line_total) AS revenue
FROM sales
GROUP BY ROLLUP (country, category)
ORDER BY GROUPING(country), country, GROUPING(category), category;

Using coalesce(country, 'ALL') instead is a classic bug: it mislabels genuine NULLs as totals.

Cheat sheet

Need	Use
Conditional aggregate	agg(...) FILTER (WHERE ...)
Deterministic collection	array_agg(x ORDER BY ...)
Nested JSON in one query	jsonb_agg(jsonb_build_object(...))
"Every / any" check	bool_and / bool_or
Median and quartiles	percentile_cont(...) WITHIN GROUP
Several group levels, one scan	GROUPING SETS / ROLLUP / CUBE
Distinguish subtotal from NULL	GROUPING(col)