Question: I would like to create a query that can return a list of all days of the month with a sum of all duration for each day including zero for each day that has no data.
Database: PostgreSQL 9.x
Table structure (only relevant columns shown):
-------------------------------------------------------------
| id | start_at | finish_at | duration |
-------------------------------------------------------------
| 1 | 2015-08-14 14:01:00 | 2015-08-15 13:59:00 | 86280 |
-------------------------------------------------------------
I have the following query for grabbing the data I want but the part that is missing is the days between with zero and I'm not entirely sure on the best approach to achieve the desired outcome.
SELECT date_trunc('day', start_at) AS "day" , sum(duration) AS "duration"
FROM time_logs
JOIN users ON time_logs.user_id = users.id
JOIN account_users ON account_users.user_id = users.id
JOIN categories ON categories.id = time_logs.activity_id
WHERE account_users.account_id IN (1,2,3)
AND time_logs.start_at BETWEEN '2017-03-01 00:00:00 +1000' AND '2017-03-31 00:00:00 +1000'
AND categories.report_group = 'Segment1'
GROUP BY 1
ORDER BY 1
date_series
left joincurrent_query
, converting anysum(duration)=NULL
to zero; if you still can't get it working then post back with an updated version of your working sql ... it shouldn't take much tweaking to get it to work