SQL Count on dates when no orders exist

Question

I am trying to count on rows that dont come up in the join. I want a count of 0 for days with no orders.

Want this query to return 0 next to dates that don't have any and not return null.

SELECT           days.date,
                 COALESCE(Count(service_orders.id)::int) AS count
FROM             (
                        SELECT To_char( Date_trunc('day', (CURRENT_DATE + offs)), 'YYYY-MM-DD' )            AS date,
                               trim(both ' ' FROM to_char(date_trunc('day', (CURRENT_DATE + offs)), 'day')) AS day
                        FROM   generate_series(0, 60, 1)                                                    AS offs ) days
RIGHT OUTER JOIN
                 (
                           SELECT    service_order.id,
                                     to_char( date_trunc('day', service_order.date), 'YYYY-MM-DD' ) service_order_day
                           FROM      service_order
                           LEFT JOIN order_
                           ON        service_order.order_id = order_.id) service_orders
ON               days.date = service_orders.service_order_day
GROUP BY         days.date
ORDER BY         days.date ASC;

2023-06-22  6
2023-06-23  5
NULL        15312

I don't want null! And I want days that have a count of 0 as well.

So what I want is

2023-06-22  6
2023-06-23  5
2023-06-24  0
2023-06-25  0
...

Answer 1

As I understand your question, you want the count of service orders for each day over the next 60 days (including days without any order).

You seem to be overcomplicating things here:

• you can use generate_series directly on dates (no need for an intermediate step with a series of numbers)
• the next step would be to join the service order tables to count the rows on each date; this can be done efficiently with a lateral join and aggregation (note that we don't need to truncate convert the dates back and forth to strings, we can do direct filtering against half-open intervals)
• as far as I can see, table order_ is superfluous in the query

Doesn't this do what you ask for?

select d.dt, so.cnt
from generate_series(current_date, current_date + interval '60 day', interval '1 day') as d(dt)
cross join lateral (
    select count(*) cnt
    from service_order so
    where so.date >= d.dt and so.date < d.dt + interval '1 day'
) so

We could also use a left join and outer aggregation:

select d.dt, count(so.id) cnt
from generate_series(current_date, current_date + interval '60 day', interval '1 day') as d(dt)
left join service_order so on so.date >= d.dt and so.date < d.dt + interval '1 day'
group by d.dt