# Advanced PostgreSQL Aggregations

## Schema

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

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/2f6cba06-e96c-41c5-99b5-56134ade0f33.png align="center")

## Setup

```sql
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:

```sql
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:

```sql
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

```sql
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

```sql
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

```sql
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

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

### ARRAY_AGG

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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 BY`s in one scan:

```sql
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), ())`:

```sql
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), ())`:

```sql
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:

```sql
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)` |