I Have totals by date, - want to get total "to date" for each date

Database
Enthusiast

I Have totals by date, - want to get total "to date" for each date

Hello - 

I'm hoping someone can give a little guidance  on how to create a table that includes a "sum to date" - I believe there must be a simple way to do this but I have been unable to find a thread with this topic.

I am starting with a table(totals_by_date) with two columns - _date,total.

( like this):

totals_by_date

_date total
2014-07-01 3
2014-06-30 10
2014-06-27 5
2014-06-26 11
2014-06-25 5

And I am looking to create a table (totals_w_to_date) from the data in this table with 3 columns:  _date,total,sum_to_date

(like this):

_date         total    sum_to_date
2014-07-01 3 34
2014-06-30 10 31
2014-06-27 5 21
2014-06-26 11 16
2014-06-25 5 5

This is my first thread on the forums, and I am very green with my sql joins at this point - if anyone can help or point me in the direction to a thread on a similar topic, any help would be greatly appreciated. 

Please let me know if you need more detail. Thanks.


3 REPLIES
Senior Apprentice

Re: I Have totals by date, - want to get total "to date" for each date

No need for a join, this is a basic task for a "Windowed Aggregate Function", a cumulative sum:

select
_date, total,
sum(total)
over (order by _date
rows unbounded preceding) as sum_to_date
from tab
order by _date desc
Enthusiast

Re: I Have totals by date, - want to get total "to date" for each date

update- still can't get it quite right, but was able to get close after doing some digging/reading on OVER clauses for SUM to get running totals.

Also wanted to give more detail as to what I'm working with and where I'm at -

I have a table which includes all dates (sandbox.vo_dates) in one column

and another table which includes all orders(sandbox.vo_orders) with multiple columns, including order_date, and ship_date

all orders that have a ship_date also have an order_date.

here's my current statement:

sel

ship_date _date
,count(ship_date) activations
,sum( count(ship_date)) over (order by ship_date) sum_activations_td

from sandbox.vo_dates a

left join (
sel
order_date
,ship_date
from sandbox.vo_orders
where order_date is not null
and ship_date is not null
group by 1,2) b on b.ship_date=a._date

where _date< current_date
group by 1
order by 1 desc;

This gives me the following result:

_date              shipments     sum_shipments_td
2014-07-01 3 34
2014-06-30 10 34
2014-06-27 5 34
2014-06-26 10 34
2014-06-25 4 34

which is close - but not the end result that I'm looking for, as I'd like the sum_shipments_td column to display the running total as of the date in the _date column. 

Enthusiast

Re: I Have totals by date, - want to get total "to date" for each date

Wow thanks for the speedy response, Dieter! Was too busy working on updating to realize you had already provided an excellent solution.

Appreciate the help.