0
223
ViewsDisplay Dates that have 0 count data
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
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!
·
Santiago Trujillo
2 answers
Answer question0
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;
·
Santiago Trujillo
Report
0
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;
·
Santiago Trujillo
Report
Answer question