I am attempting to query my DB for a date range using BETWEEN 'date_1' AND 'date_2' BUT i want the query to return the date if the sum(column_name) is 0 (no rows returned). I have tried multiple ways using COALESCE and sub selects but to no avail.
Query basically looks like:
SELECT sum(users), date FROM table WHERE date BETWEEN 'date_1' AND 'date_2'.
And should return rows for each date regardless if there is data, for the dates between date_1 and date_2.
you can try with generate_series function in posgresql.
so it will generate first dates; and the dates which are not available will be zero;
select coalesce(sum(yt.users), 0),
from generate_series('date_1'::timestamp, 'date_2'::timestamp, '1 day'::interval) as t(dates)
left join your_table as yt on yt.date = t.dates
group by t.dates;