0

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-15'
            AND tasks.site = sites.id
        ) ALIAS
      ) AS num_tasks,
      count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   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?

6
  • Postgres 8.1 is long dead and forgotten - it's time to update to a supported and maintained version. Oct 18, 2016 at 21:03
  • I am unable to update to a supported and maintained version or else I would have. Thanks for the editing assist. Oct 18, 2016 at 21:06
  • 1
    Is ztime a DATE? It would be best if you added the CREATE TABLE statements in the question, Oct 18, 2016 at 21:22
  • 1
    If You create data structure in SQLFiddle it will be more easy to help, but general mistake it is subquery for numtask it not depend from main query - it always calculate same data. You must use any reference to parent query - for example name parent measurement t1, subquery measurement t2 - it will be WHERE t2.date = t1.date
    – a_vlad
    Oct 18, 2016 at 21:33
  • Does the num_tasks have to count all tasks, even those that do not belong to the site with 'AA-10'? Oct 19, 2016 at 0:22

1 Answer 1

2

I don't think you need the subquery. Would this work?

SELECT sites.abbrev,
       measurements.ztime::date AS period_start,
       count(DISTINCT measurements.task) AS num_tasks,
       count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   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 ;
4
  • what if sites.abbrev != 'AA-10'?
    – a_vlad
    Oct 18, 2016 at 21:35
  • Does the num_tasks have to count all tasks, even those that do not belong to the site with 'AA-10'? Oct 18, 2016 at 21:37
  • No ypercube, the num_tasks only has to count tasks for the corresponding site. In this case, that would be AA-10. I am going to try your answer above. Oct 19, 2016 at 14:59
  • 1
    The solution provided by ypercube worked! Thank you very much! Issued solved. The query is very fast too! Oct 19, 2016 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.