Generating the rows that are not there

The zero that never appears

A grouped query can only report on rows that exist. In March, the Kochi branch made no sales. GROUP BY branch, month therefore produces no pile for Kochi in March, and no row.

The consequences are worse than a missing line in a table. A chart draws a straight line from February to April, implying steady trade through a month with none. A month-on-month growth calculation compares March to January without saying so. A report emailed to a regional manager lacks the row that would have prompted a question.

Missing rows are much harder to notice than wrong numbers, because nothing is there to look wrong.

Generate the frame first

The fix is to build the complete set of rows you want (the spine) and left join the data onto it. In Postgres:

SELECT generate_series(date '2026-01-01', date '2026-12-01', interval '1 month')::date AS month;

Twelve rows, whether or not anything happened in them. Now cross join it with the branches to get every combination:

WITH months AS (
  SELECT generate_series(date '2026-01-01', date '2026-06-01', interval '1 month')::date AS month
),
spine AS (
  SELECT b.id AS branch_id, b.name, m.month
  FROM branches b CROSS JOIN months m
)
SELECT
  s.name,
  s.month,
  coalesce(sum(o.amount), 0) AS revenue,
  count(o.id)                AS orders
FROM spine s
LEFT JOIN orders o
       ON o.branch_id = s.branch_id
      AND o.placed_at >= s.month
      AND o.placed_at <  s.month + interval '1 month'
GROUP BY s.name, s.month
ORDER BY s.name, s.month;

Read the three parts that make it work.

The CROSS JOIN is deliberate: this is the one place a Cartesian product is exactly what you want. Six months times four branches is twenty-four rows, guaranteed.

The date condition sits in the ON clause, not in WHERE. This is the LEFT JOIN trap from the joins lesson: a WHERE o.placed_at >= ... would discard the spine rows that matched nothing, undoing the entire exercise.

coalesce(sum(o.amount), 0) turns the NULL from an empty pile into a zero. And note count(o.id), not count(*): the unmatched spine row still produces one output row, so count(*) would report one order in a month with none.

Portable ways to make a spine

generate_series is Postgres and DuckDB. Elsewhere:

A recursive CTE works in SQLite, MySQL 8 and SQL Server, each with its own date arithmetic. In SQLite:

WITH RECURSIVE months(m) AS (
  SELECT date('2026-01-01')
  UNION ALL
  SELECT date(m, '+1 month') FROM months WHERE m < '2026-06-01'
)
SELECT * FROM months;

A permanent calendar table is the approach most data warehouses take, and it is better than either. One row per date from 2015 to 2035, with columns for the month, quarter, fiscal year, weekday, and whether it is a public holiday in each market you operate in. Ten minutes to build, and every date question afterwards becomes a join instead of an argument about fiscal quarters.

CREATE TABLE calendar AS
SELECT d::date AS day,
       date_trunc('month', d)::date AS month_start,
       extract(isodow FROM d)       AS weekday,
       extract(isodow FROM d) >= 6  AS is_weekend
FROM generate_series(date '2015-01-01', date '2035-12-31', interval '1 day') d;

The holiday column cannot be computed and has to be loaded, which is exactly why having the table matters: it gives holidays somewhere to live, instead of being hard-coded into whichever query needed them last.

The same trick for categories

Spines are not only for dates. Any report that must show every category, including the empty ones, is the same shape: start from the dimension table, left join the facts.

SELECT c.title, count(e.student_id) AS students
FROM courses c
LEFT JOIN enrolments e ON e.course_id = c.id
GROUP BY c.title;

Courses with nobody enrolled appear with zero, which is very often the row somebody actually needs to see.

The judgement call

Not every gap should be filled with a zero. A missing month for a branch that opened in April is not zero revenue, it is not applicable, and printing zero implies a business that existed and sold nothing. Bound the spine by the entity’s own lifetime:

FROM branches b
CROSS JOIN months m
WHERE m.month >= date_trunc('month', b.opened_on)
  AND (b.closed_on IS NULL OR m.month <= date_trunc('month', b.closed_on))

The distinction between “zero happened” and “the question does not apply” is real, and it is the same distinction as zero versus NULL from the earlier lesson. A spine gives you the power to assert one of them; use it to assert the one that is true.