How does the fill missing dates query work?

generate_series produces one timestamp for every day in the range. A LEFT JOIN from the calendar to the orders keeps every day, even those with no matching order. COALESCE turns the resulting null sum into 0, and COUNT(o.id) counts only matched orders, so empty days show 0.

What commonly goes wrong with fill missing dates in SQL?

COUNT(*) instead of COUNT(o.id) counts the calendar row itself and reports 1 for empty days.

Fill Missing Dates in SQL

Filling missing dates builds a complete calendar and joins your data to it, so days or months with no records show as zero instead of disappearing.

Example query

This example assumes: orders(id, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT d::date AS day,
       COALESCE(SUM(o.total_amount), 0) AS sales,
       COUNT(o.id) AS orders
FROM generate_series(DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day') AS d
LEFT JOIN orders o ON o.order_date = d::date
GROUP BY d
ORDER BY d;

How it works

generate_series produces one timestamp for every day in the range.
A LEFT JOIN from the calendar to the orders keeps every day, even those with no matching order.
COALESCE turns the resulting null sum into 0, and COUNT(o.id) counts only matched orders, so empty days show 0.

MySQL, BigQuery, SQL Server and SQLite differences

generate_series is PostgreSQL. In MySQL 8+, SQLite and SQL Server use a recursive CTE that adds one day per step.
BigQuery uses UNNEST(GENERATE_DATE_ARRAY(start, end)) as the calendar.

Common mistakes

COUNT(*) instead of COUNT(o.id) counts the calendar row itself and reports 1 for empty days.
If you filter on the orders table in the WHERE clause, the LEFT JOIN turns into an inner join; put such conditions in the ON clause.
Choose the calendar range deliberately; leading and trailing empty days can mislead a chart.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show sales for every day last month, including days with no sales.
Give me a daily signup count with zeros for empty days.