Aggregations and GROUP BY

Aggregation functions collapse multiple rows into a single summary value. They’re how you answer analytical questions — “how much revenue did we make last month?”, “which departments have the highest average salary?”, “how many customers placed more than five orders?” Without aggregations, you’d need to load all the raw rows into your application and compute these values there, which doesn’t scale.

The Core Aggregate Functions

The five standard aggregates — COUNT, SUM, AVG, MIN, MAX — cover the vast majority of summarization needs. They all ignore NULL values in their input (except COUNT(*), which counts rows regardless of NULLs).

SELECT
  COUNT(*)            AS total_rows,       -- counts all rows including NULLs
  COUNT(email)        AS rows_with_email,  -- excludes rows where email IS NULL
  SUM(total)          AS revenue,
  AVG(total)          AS avg_order_value,
  MIN(created_at)     AS first_order,
  MAX(created_at)     AS latest_order
FROM orders;

COUNT(*) counts every row. COUNT(column) skips NULLs — useful for counting how many rows actually have a value in an optional column.

GROUP BY

GROUP BY is what turns aggregate functions from “one number for the whole table” into “one number per group.” It splits rows into groups based on one or more columns, then applies the aggregate function independently to each group. This is how you answer “per customer”, “per department”, or “per month” questions.

-- Orders and revenue per customer — one row per customer_id
SELECT
  customer_id,
  COUNT(*)        AS order_count,
  SUM(total)      AS total_spent,
  AVG(total)      AS avg_order
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Every column in SELECT that is not an aggregate function must appear in GROUP BY. PostgreSQL will error if you forget one.

Grouping by multiple columns

When you group by multiple columns, each unique combination of those columns forms its own group. This lets you break down metrics along two dimensions simultaneously.

-- Monthly revenue broken down by status — each month/status pair is a separate group
SELECT
  DATE_TRUNC('month', created_at) AS month,
  status,
  COUNT(*)                        AS count,
  SUM(total)                      AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at), status
ORDER BY month, status;

HAVING: Filtering Groups

WHERE runs before grouping and filters individual rows. HAVING runs after grouping and filters the resulting groups. The key distinction: you can use aggregate functions in HAVING but not in WHERE. Use HAVING whenever your filter condition is based on a computed group statistic.

-- Only customers who placed more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- Departments where average salary exceeds 80000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 80000;

You can combine WHERE and HAVING in the same query — WHERE narrows the rows fed into the aggregation, HAVING narrows the groups in the output:

-- Active departments with at least 10 employees
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'          -- filter rows first (before grouping)
GROUP BY department_id
HAVING COUNT(*) >= 10            -- then filter groups (after grouping)
ORDER BY headcount DESC;

COUNT(DISTINCT column)

COUNT(DISTINCT column) counts unique values rather than total rows. This is essential for metrics like “unique active users” or “distinct customers who placed orders” — where counting raw rows would overcount users who appear multiple times.

-- Distinct customers who placed orders each month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(DISTINCT customer_id)     AS unique_customers
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

The FILTER Clause

FILTER is a clean way to compute multiple conditional aggregates in a single pass over the data. Without it, you’d need multiple subqueries or verbose CASE WHEN expressions. With FILTER, the intent is clear and the query touches the table only once.

-- Order counts and revenue broken down by status — all in one query
SELECT
  COUNT(*)                                     AS total,
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
  COUNT(*) FILTER (WHERE status = 'shipped')   AS shipped,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'shipped') AS shipped_revenue
FROM orders;

The equivalent with CASE WHEN is more verbose and harder to scan:

-- Same result, harder to read — FILTER is preferred
SELECT
  COUNT(*),
  SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

Aggregates with JOINs

Aggregating across joined tables is a common pattern — you want the customer’s name alongside their order totals, which requires joining before aggregating. The key detail: use LEFT JOIN if you want customers with zero orders to appear in the result.

-- Revenue and order count per customer, including customers with no orders
SELECT
  c.name,
  COUNT(o.id)   AS orders,   -- counts NULL as 0 for customers with no orders
  SUM(o.total)  AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY revenue DESC NULLS LAST;

GROUPING SETS and ROLLUP

ROLLUP generates subtotals and grand totals alongside the detail rows in a single query. This is more efficient than running separate queries for each level of aggregation and then combining them in application code.

-- Revenue by year, then by year+month, then a grand total — all in one result
SELECT
  EXTRACT(year  FROM created_at) AS yr,
  EXTRACT(month FROM created_at) AS mo,
  SUM(total)                     AS revenue
FROM orders
GROUP BY ROLLUP (
  EXTRACT(year FROM created_at),
  EXTRACT(month FROM created_at)
)
ORDER BY yr NULLS LAST, mo NULLS LAST;
-- NULL in yr or mo indicates a subtotal/grand total row

NULL in the yr or mo column indicates a subtotal or grand total row.