Display Dates that have 0 count data

user_9039196 0
Viewed 63

I have a query like this

SELECT
  d.create_at :: DATE AS dates,
  count(r.id) AS usages
FROM reports r LEFT JOIN dispatches d ON r.dispatch_id = d.id
  LEFT JOIN messages m ON d.message_id = m.id
WHERE r.status = 'SUCCESS' AND r.update_at :: DATE BETWEEN DATE '2020-05-01' AND '2020-05-10' AND m.client_id = 6
GROUP BY dates
ORDER BY dates;

that will generate result like this

result

there's no data on date 2020-05-03 and 2020-05-08 so they wont showed on result

How do I add those dates that have no data and give the count data 0 as value?

Sorry for I'm new to Postgres and DB in general

I've followed several solution from everywhere doesn't seem to get the result I want yet

I try to use generate_series but no result came out

SELECT
  x.day AS dates,
  count(r.id) AS usages
FROM (
   SELECT generate_series(timestamp '2020-05-01'
                        , timestamp '2020-05-10'
                        , interval '1 day')::date
   ) x(day)
    left join dispatches d on d.create_at = x.day
    LEFT JOIN reports r ON d.id = r.dispatch_id
    LEFT JOIN messages m ON d.message_id = m.id
WHERE r.status = 'SUCCESS' AND r.update_at :: DATE BETWEEN DATE '2020-05-01' AND '2020-05-10' AND m.client_id = 6
GROUP BY dates
ORDER BY dates;

Sorry if I'm missing something obvious. Any help would be appreciated thank you!

left-join postgresql sql
2 Answers

You were close, but your WHERE clause turns the outer join back into an inner join. You need to move that condition into the JOIN condition. 

SELECT x.day AS dates,
       count(r.id) AS usages
FROM (
   SELECT generate_series(timestamp '2020-05-01'
                        , timestamp '2020-05-10'
                        , interval '1 day')::date
) x(day)
  left join dispatches d on d.create_at = x.day
  LEFT JOIN reports r 
         ON d.id = r.dispatch_id
        and r.status = 'SUCCESS' 
        AND r.update_at::DATE BETWEEN DATE '2020-05-01' AND '2020-05-10' 
        AND m.client_id = 6
    LEFT JOIN messages m ON d.message_id = m.id
GROUP BY dates
ORDER BY dates;
user_3303150

Please use below query,

SELECT
  dt.date_seq :: DATE AS dates,
  count(r.id) AS usages
FROM reports r LEFT JOIN dispatches d ON r.dispatch_id = d.id
LEFT JOIN messages m ON d.message_id = m.id
FULL OUTER JOIN (select CURRENT_DATE + i as date_seq from generate_series(date '2020- 
05-01'- CURRENT_DATE, date '2020-05-10' - CURRENT_DATE ) i)
dt
ON d.create_at = dt.date_seq
WHERE r.status = 'SUCCESS' AND r.update_at :: DATE BETWEEN DATE '2020-05-01' AND 
'2020-05-10' AND m.client_id = 6
GROUP BY dates
ORDER BY dates;
user_44736150
Related