0

So I want to get cumulative data, for every day since a start date until an end date (or today). I have constructed a query that will get the data from a table, but for some reason, some dates are missing. My left join isn't working and I am not getting the desired results. Can anybody point out my mistake?

(also, as a small detail, how do I only get the date and not the timestamp?)

Current result (note the missing dates):

          date          |   sum    
------------------------+----------
 2012-09-01 00:00:00+02 |    8.000
 2012-09-03 00:00:00+02 |   29.300
 2012-09-07 00:00:00+02 |   31.300

Expected result (a missing date behaves as distance 0, so sum shouldn't increase):

          date          |   sum    
------------------------+----------
 2012-09-01 00:00:00+02 |    8.000
 2012-09-02 00:00:00+02 |    8.000
 2012-09-03 00:00:00+02 |   29.300
 2012-09-04 00:00:00+02 |   29.300
 2012-09-05 00:00:00+02 |   29.300
 2012-09-06 00:00:00+02 |   29.300
 2012-09-07 00:00:00+02 |   31.300

Query:

WITH dates  as (SELECT min(date) as start_date,
                       max(date) as end_date
                from training_training
                where athlete_id = 1)

SELECT  distinct(d.date),
        sum(distance) OVER (ORDER BY d.date)
FROM
      (
       SELECT generate_series(start_date, end_date, interval '1 day') as date
       FROM dates
      ) d
LEFT JOIN training_training
     ON         d.date = training_training.date
     WHERE athlete_id  =  1
     AND   kind        in ('t', 'd', 'i', 'w')

GROUP BY d.date, distance
ORDER BY d.date
  • Move the where condition into join condition. A where condition on the "outer" table turns the outer join into an inner join. – a_horse_with_no_name Nov 27 '15 at 23:01
0

As inspired by a_horse_with_no_name, I moved the right part of the join to a subquery so the where clause doesn't interfere with the join condition

This works as intended:

WITH dates  as (SELECT min(date) as start_date,
                       max(date) as end_date
                from training_training
                where athlete_id = 1)

SELECT  distinct(d.date),
   sum(distance) OVER (ORDER BY d.date)
FROM
  (
    SELECT generate_series(start_date, end_date, interval '1 day') as date
    FROM dates
  ) d
LEFT JOIN
  (
    SELECT
      date,
      distance
    FROM training_training
    WHERE athlete_id = 1
          AND kind IN ('t', 'd', 'i', 'w')
  ) t
ON d.date = t.date

GROUP BY d.date, distance
ORDER BY d.date

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.