Add a running total/cumulative column

Question

I am trying to get a running total for the count of sales per Actual_Sale_Date in my table for the year 2015 but thus far all my attempts been futile. I took a look at the attached link and emulated the process but to no gain. Can someone help out here please?

Calculating Cumulative Sum in PostgreSQL

select Actual_Sale_Date, extract(week from Actual_Sale_Date) as week_number, 
count(*) from mytable
where extract(year from Actual_Sale_Date) = 2015

Result: (Requested Running_Total)

 Actual_Sale_Date         week_number          count           running_total
2015-01-04 00:00:00           1                  1                 1
2015-01-06 00:00:00           2                  3                 4
2015-01-08 00:00:00           2                  4                 8
2015-01-09 00:00:00           2                  5                 13
2015-01-11 00:00:00           2                  1                 14
2015-01-15 00:00:00           3                  2                 16
2015-01-21 00:00:00           4                  1                 17
2015-01-23 00:00:00           4                  4                 21
2015-01-24 00:00:00           4                  1                 22
2015-01-26 00:00:00           5                  2                 24

I would alias that table, and then do something with a subquery that sums count up to the sale date for that row. — Denziloe, Mar 07 '17 at 22:35

score 3 · Accepted Answer · answered Mar 07 '17 at 22:34

Just use window functions:

select Actual_Sale_Date, extract(week from Actual_Sale_Date) as week_number, 
       count(*),
       sum(count(*)) over (order by Actual_Sale_Date)
from ??
where extract(year from Actual_Sale_Date) = 2015
group by Actual_Sale_Date, extract(week from Actual_Sale_Date);

Add a running total/cumulative column

1 Answers1