anil.kumar
20th November 2009, 01:32 PM
Often reports and data sets have a requirement to keep running totals.
With conventional SQL, this is VERY cumbersome, but easy with Oracle analytic functions.
Perform a simple select where we list orders in order of ORDERID but keep a running total.
select country, city, CompanyName, OrderDate, Orderid, soldby, OrderTotal,
sum (OrderTotal) over (ORDER by ORDERID) as "running total"
from OrdersMatrix ORDER by ORDERID
In the above, note the same sort sequence is kept on the main query and in the analytic function.
Similarly, we could keep a running total per country using the
OVER (PARTITION BY COUNTRY) clause, or month or whatever we wish.
select country, OrderDate, Orderid, soldby, OrderTotal,
sum(OrderTotal) over (PARTITION by COUNTRY ORDER by ORDERID) as "CTRY runtot" ,
sum(OrderTotal) over (ORDER by country, ORDERID ) as "Running Total"
from OrdersMatrix ORDER by country, ORDERID
With conventional SQL, this is VERY cumbersome, but easy with Oracle analytic functions.
Perform a simple select where we list orders in order of ORDERID but keep a running total.
select country, city, CompanyName, OrderDate, Orderid, soldby, OrderTotal,
sum (OrderTotal) over (ORDER by ORDERID) as "running total"
from OrdersMatrix ORDER by ORDERID
In the above, note the same sort sequence is kept on the main query and in the analytic function.
Similarly, we could keep a running total per country using the
OVER (PARTITION BY COUNTRY) clause, or month or whatever we wish.
select country, OrderDate, Orderid, soldby, OrderTotal,
sum(OrderTotal) over (PARTITION by COUNTRY ORDER by ORDERID) as "CTRY runtot" ,
sum(OrderTotal) over (ORDER by country, ORDERID ) as "Running Total"
from OrdersMatrix ORDER by country, ORDERID