Join a count query on generate_series() and retrieve Null values as ‘0’

by

Untangled, simplified and fixed, it might look like this:

SELECT to_char(s.tag,'yyyy-mm') AS monat
     , count(t.id) AS eintraege
FROM  (
   SELECT generate_series(min(date_from)::date
                        , max(date_from)::date
                        , interval '1 day'
          )::date AS tag
   FROM   mytable t
   ) s
LEFT   JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1   
GROUP  BY 1
ORDER  BY 1;

db<>fiddle here

Among all the noise, misleading identifiers and unconventional format the actual problem was hidden here:

WHERE version = 1

You made correct use of RIGHT [OUTER] JOIN. But adding a WHERE clause that requires an existing row from mytable converts the RIGHT [OUTER] JOIN to an [INNER] JOIN effectively.

Move that filter into the JOIN condition to make it work.

I simplified some other things while being at it.

Better, yet

SELECT to_char(mon, 'yyyy-mm') AS monat
     , COALESCE(t.ct, 0) AS eintraege
FROM  (
   SELECT date_trunc('month', date_from)::date AS mon
        , count(*) AS ct
   FROM   mytable
   WHERE  version = 1     
   GROUP  BY 1
   ) t
RIGHT JOIN (
   SELECT generate_series(date_trunc('month', min(date_from))
                        , max(date_from)
                        , interval '1 mon')::date
   FROM   mytable
   ) m(mon) USING (mon)
ORDER  BY mon;

db<>fiddle here

It’s much cheaper to aggregate first and join later – joining one row per month instead of one row per day.

It’s cheaper to base GROUP BY and ORDER BY on the date value instead of the rendered text.

count(*) is a bit faster than count(id), while equivalent in this query.

generate_series() is a bit faster and safer when based on timestamp instead of date. See:

More Related Contents:

  1. How to perform a select query in a DO block?
  2. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  3. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  4. PL/pgSQL checking if a row exists
  5. Best way to count rows by arbitrary time intervals
  6. Call a set-returning function with an array argument multiple times
  7. Compute percents from SUM() in the same SELECT sql query
  8. Postgres analogue to CROSS APPLY in SQL Server
  9. Record returned from function has columns concatenated
  10. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  11. How to find a table having a specific column in postgresql
  12. PostgreSQL JOIN with array type with array elements order, how to implement?
  13. Postgres function returning table not returning data in columns
  14. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  15. Calculate working hours between 2 dates in PostgreSQL
  16. Check if NULL exists in Postgres array
  17. How to use SQL LIKE condition with multiple values in PostgreSQL?
  18. How to create a new database with the hstore extension already installed?
  19. PostgreSQL – dynamic value as table name [duplicate]
  20. Join vs. sub-query
  21. Refactor a PL/pgSQL function to return the output of various SELECT queries
  22. Can I use CASE statement in a JOIN condition?
  23. Subqueries vs joins
  24. Column ‘mary’ does not exist
  25. Is a JOIN faster than a WHERE?
  26. Delete parent if it’s not referenced by any other child
  27. SQL query to get most recent row for each instance of a given key
  28. Postgres constraint for unique datetime range
  29. Create PostgreSQL ROLE (user) if it doesn’t exist
  30. How to use a ring data structure in window functions

Leave a Comment