Group by day of the month (series) as well as a location

Question

I have come across the following link How to get list day of month data per month in postgresql and am building on this for my own query. Which shows a simple use of generate series for a listing of dates. I have a table that has dates, number of users and a location, which I would like to report on monthly, and on the days which have no data, simply show zero. I think the issue I am having is with the grouping of the location, as that is where my results go astray currently from what is expected.

My data (table = reserve)

Date | Users | Location
-----------------------
2021-05-02  | 3 |   1100<br>
2021-05-24 | 4  |   1000<br>
2021-05-26 |  6 |   1000<br>
2021-05-28 |  7 |  1100<br>
2021-05-29 |  4 |  1100<br>
2021-05-27 |  3 |  1000<br>
etc.

If I use the generate_series for the entire month (generate_series('2021-06-01', '2021-10-31', '1 day'::interval) and then join to the reserve table for each of the locations, the issue is that the group by will exclude the blank days on the join.

I am hoping to achieve:

Date          
2021-05-01 |     0  |    1000<br>
2021-05-02    0     1000<br>
....<br>
2021-05-24    4     1000<br>
Until end of month<br>
2021-05-01    0     1100<br>
2021-05-02    3     1100<br>
....
Until end of month

Thank you in advance.

Answer 1

It's hard to tell exactly what you're after based on the example data from your tables but if you don't want to eliminate rows where there is no match this is exactly what a LEFT JOIN is for.

For example:

SELECT * FROM generate_series('2021-06-01', '2021-10-31', '1 day'::interval) as d 
LEFT JOIN reserve r ON r.the_date = d;

This will keep all the days in the sequence and just returns null for the columns where there was no match for that day. You can see this in action with this SQL fiddle example