All Forums Database
voleary 14 posts Joined 02/14
02 Jul 2014
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):


_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.


dnoeth 4628 posts Joined 11/04
02 Jul 2014

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

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




voleary 14 posts Joined 02/14
02 Jul 2014

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:


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 (
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. 

voleary 14 posts Joined 02/14
02 Jul 2014

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.

You must sign in to leave a comment.