Why does a GROUP BY report skip days with no sales, and how do you emit a row for every day?

## The rule behind the symptom `GROUP BY` partitions the rows it is given. A group exists if and only if some input row carries that key value. It follows that the result can only ever contain keys the data contains — grouping never invents a key, and there is no such thing as an empty group waiting to report zero. So a per-day revenue report shows the days that had sales. Days with none are absent, not zero. The same rule produces every version of this complaint: categories with no products, regions with no customers, statuses no order is currently in, hours with no errors. The distinction matters because "absent" and "zero" mean different things to a consumer. A chart drawn from absent rows joins the two neighbouring days with a straight line and hides the outage; a table shows fewer rows than the reader expected and quietly shifts week-over-week comparisons. ## Why no amount of aggregate tinkering fixes it Candidates often reach for the aggregate first: swap `SUM` for `COUNT(*)`, wrap it in `COALESCE`, add `HAVING COUNT(*) >= 0`. None of these can work, because every one of them operates on a group that already exists. `COALESCE(SUM(amount), 0)` turns a NULL measure into 0 *within* a group; it cannot create the missing group. `HAVING` filters existing groups. The missing key values simply are not in the query yet. ## Supplying the key domain The fix is structural: get the complete set of key values from somewhere that has it, and outer-join the aggregated facts to it. For time series that source is a **calendar table** — a small table with one row per date, which most warehouses keep anyway because it also carries fiscal periods, holidays and week numbers. Some engines can generate a series of dates on the fly with a built-in function, but those functions are engine-specific; a stored calendar table is the portable answer and is usually more useful besides. For a categorical dimension, the source is the dimension table itself: `categories`, `regions`, a status enum table. ```sql WITH daily AS ( SELECT CAST(sold_at AS DATE) AS sale_day, SUM(amount) AS revenue, COUNT(*) AS order_count FROM sales WHERE sold_at >= TIMESTAMP '2026-03-01 00:00:00' GROUP BY CAST(sold_at AS DATE) ) SELECT c.cal_date, COALESCE(d.revenue, 0) AS revenue, COALESCE(d.order_count, 0) AS order_count FROM calendar c LEFT JOIN daily d ON d.sale_day = c.cal_date WHERE c.cal_date >= DATE '2026-03-01' AND c.cal_date < DATE '2026-04-01' ORDER BY c.cal_date; ``` Three things make that shape work: 1. **Aggregate first, join second.** The CTE reduces the facts to one row per day before the join, so the outer join is one-to-at-most-one and there is no risk of the join multiplying rows before aggregation. 2. **The date range lives on the calendar side.** The reporting window is a property of the axis you want to draw, not of the facts. 3. **`COALESCE` supplies the zero.** Unmatched calendar rows get NULL measures from the outer join; converting them to `0` is a display decision you should make explicitly, since NULL and 0 are different claims — "we have no record" versus "we recorded nothing". ## The trap that turns the fix back into the bug If you skip the pre-aggregation and join raw facts to the calendar, any additional predicate on the fact side must be placed carefully: a fact-side condition written as an ordinary filter after the join discards the very rows the outer join created for empty days, silently restoring the original symptom. Aggregating in a CTE first sidesteps the question entirely, which is why it is the pattern worth memorising. ## Deciding whether you want the zeros at all Dense output is not automatically better. Filling every gap for a sparse key space is expensive and noisy — one row per product per day across a large catalogue is mostly zeros. Reasonable practice is to densify only the axis a chart plots (usually time), keep other dimensions sparse, and let the presentation layer handle gaps when the volume is large. And be honest about the semantics: for a period that has not happened yet, or a store that had not opened, a zero is a false statement and NULL is the truthful one. ## Summary Missing rows in a grouped report are not a bug in `GROUP BY`; they are its definition. Groups come from data. When the report needs the full key domain, the domain has to come from a table that holds it, joined to the pre-aggregated facts, with the empty slots given an explicit value.