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 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
No need for a join, this is a basic task for a "Windowed Aggregate Function", a cumulative sum:
over (order by _date
rows unbounded preceding) as sum_to_date
order by _date desc
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:
,sum( count(ship_date)) over (order by ship_date) sum_activations_td
from sandbox.vo_dates a
left join (
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;
_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.
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.