nlqdb

Solve · Analysts and PMs

How do I count rows per day in SQL, including days with zero rows?

If your per-day counts skip the days with no rows — a signup chart with holes where the zeros should be — ask in plain English. nlqdb compiles the `generate_series` calendar spine, LEFT JOINs your rows onto it, runs it in Postgres, and shows the SQL, so quiet days come back as 0 instead of vanishing.

Per-day counts are the first chart anyone builds — signups per day, orders per day, errors per day — and the obvious `GROUP BY created_at::date` gets it quietly wrong: days with no rows aren't in the table, so they aren't in the result, and the chart connects straight across the gap as if the quiet day never happened. The fix is a row-generating calendar spine — `generate_series(start, end, interval '1 day')` — with your table LEFT JOINed onto it. Then the second trap: after the outer join, `COUNT(*)` counts the spine row itself, so the 'zero' day reads 1 unless you count a column from your table instead.

The snippet that solves it.

> signups per day for the last 14 days, including days with zero

What nlqdb does for this

  • Ask 'signups per day for the last 14 days, including days with zero'; nlqdb compiles a `generate_series` date spine and runs it in Postgres.
  • Your rows LEFT JOIN onto the spine, so a day with no matches comes back as 0 instead of vanishing from the result.
  • The compiled SQL counts a table column — `COUNT(s.id)`, not `COUNT(*)` — so an empty day can't count its own spine row as 1.
  • Every answer shows the SQL under a trace toggle (`SK-WEB-005`) — confirm the spine's range and the join key.

Drop into any HTML page

<nlq-data goal="signups per day for the last 14 days, including days with zero"></nlq-data>

The gap-fill query you'd otherwise hand-write — the spine, the outer join, and the COUNT(*) trap — is one English goal here, with the SQL shown so you can check all three.

What this replaces

What nlqdb doesn't try to do here

  • The date range is one you name — 'last 14 days', a start and an end. nlqdb picks the spine's bounds from your English and shows them; it won't invent a reporting window.
  • A one-off read-only answer, not a live chart that refreshes itself — your dashboard or embed re-asks on its own schedule.
  • The public `<nlq-data>` embed is read-scoped — it reports the per-day counts, it doesn't write. Loading the rows goes through the SDK or `POST /v1/run`.

Questions buyers ask

How do I count rows per day in SQL, including days with zero?
Ask in plain English — 'signups per day for the last 14 days, including days with zero.' nlqdb compiles a calendar spine with `generate_series`, LEFT JOINs your table onto it, and counts a table column so empty days return 0. It runs the query in Postgres and shows the SQL, so you can confirm the range and the join key.
Why does GROUP BY date skip days with no rows?
Because `GROUP BY` can only group rows that exist — a day with no rows contributes no group, so it silently disappears from the result rather than showing 0. The fix is to generate the full calendar first (`generate_series` in Postgres) and LEFT JOIN your data onto it, so every day is a row before the count happens.
Why does my empty day show a count of 1 instead of 0?
You counted `COUNT(*)` after the outer join. The LEFT JOIN keeps one spine row for an empty day, and `COUNT(*)` counts rows — including that one — so the day reads 1. `COUNT(t.id)` counts only non-NULL matches from your table and returns 0. nlqdb compiles the column-counting shape and shows it in the SQL.
Can I fill missing dates on a Postgres database I already run?
Yes — connect it with the signed-in BYO connect verb (`nlq db connect`, `SK-DBCONN-001`; see /solve/query-existing-postgres-in-natural-language) and ask for per-day counts in place, no ETL into a separate store. The honest limits: BYO connect is signed-in only (not the public embed), and the answer is read-only — nlqdb doesn't materialise the filled series into a table for you.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Try nlqdb in 30 seconds

No sign-in. The anonymous database lasts 72 hours; adopt it with one click if you keep it.

Start with a goal →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.