- 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; 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.