How can I obtain continuous results with a PostgreSQL query?
0
votes
1
answer
567
views
I am using PostgreSQL version 8.1. I have a table of dates and tasks. The tasks have start times and end times in the table. The query below does not work as expected.
SELECT sites.abbrev,
(SELECT count(*)
FROM (
SELECT DISTINCT measurements.task
FROM measurements
JOIN tasks ON tasks.id = measurements.task
WHERE measurements.ztime >= '2016-10-10'
AND measurements.ztime = '2016-10-10'
AND measurements.ztime <= '2016-10-15')
WHERE sites.abbrev = 'AA-10'
GROUP BY sites.id,
sites.abbrev,
measurements.ztime::date
ORDER BY measurements.ztime::date;
The results are shown below:
abbrev | num_tasks | total_time
--------+-----------+------------
AA-10 | 62 | 36
AA-10 | 62 | 5
AA-10 | 62 | 58
AA-10 | 62 | 28
AA-10 | 62 | 17
(5 rows)
Each row corresponds to one 24 hour period (Ex. 2016-10-10 to 2016-10-15). The problem is that I need
num_tasks
to show me the results for each 24 hour period, not the total number as shown.
PostgreSQL version 8.1 does not allow for dates to be used with generate_series
and there are no windowing functions available either.
How can I modify my query so I can achieve the desired results?
Asked by GIS Student
(11 rep)
Oct 18, 2016, 09:00 PM
Last activity: Oct 18, 2016, 09:33 PM
Last activity: Oct 18, 2016, 09:33 PM