0

I have a bunch on time entries, they have entre_dates and hours associated with them like so.

drop table if exists data  cascade;
create table time_table (id int, entry_date date, hours numeric);
insert into time_table
values
(1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
(2, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
(3, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
(4, to_date('01-04-2019', 'dd-mm-yyyy'), 4),
(5, to_date('01-05-2019', 'dd-mm-yyyy'), 5),
(6, to_date('01-06-2019', 'dd-mm-yyyy'), 6);

For each time entry, I would like to sum all the hours that occur on and before that entry date to get a running total.

Here's my attempt but I have no idea what I'm doing:

SELECT t1.entry_date, t1.hours, htd.running_total
FROM time_table t1
INNER JOIN (
    SELECT SUM(t2.hours) AS running_total
    FROM time_table t2
    WHERE t2.entry_date <= t1.entry_date
) AS htd ON t1.id = t2.id;

I'm trying to get the following result:

Date       | Hours | Running Total
-----------------------------------
01-01-2019 | 1     | 1
01-02-2019 | 2     | 3
01-03-2019 | 3     | 6
01-04-2019 | 4     | 10
01-05-2019 | 5     | 15
01-06-2019 | 6     | 21

What's the correct way to do this query?

3

Try this

    drop table if exists data  cascade;
    create table time_table (id int, entry_date date, hours numeric);
    insert into time_table
    values
    (1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
    (2, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
    (3, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
    (4, to_date('01-04-2019', 'dd-mm-yyyy'), 4),
    (5, to_date('01-05-2019', 'dd-mm-yyyy'), 5),
    (6, to_date('01-06-2019', 'dd-mm-yyyy'), 6);

    select *,
       sum(hours) over(order by entry_date asc rows between unbounded preceding 
     and current row) as running_total
     from time_table

db fiddle here: https://www.db-fiddle.com/f/ojsazNcvgfaX66CzdRDRXm/1

  • Perfect, thanks! – Ryan King May 20 at 19:13
  • 1
    no problem, more information here regarding the window functions postgresql.org/docs/current/… – kevinnwhat May 20 at 19:14
  • I was going to suggest this too. Windowed functions (using the over command) allows you to accomplish rolling avg, sum count et al. – Daniel L. VanDenBosch May 20 at 19:38

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.