cumulative aggregation in a query

[nir_s]

Hi falks,

I have the following query:
SQL> SELECT
2 to_char(trunc(T_PPV_ORDER.CREATE_DATE,'month'),'mm/yyyy'),
3 sum(T_PPV_ORDER.PRICE)
4 FROM
5 T_PPV_ORDER
6 GROUP BY
7 trunc(T_PPV_ORDER.CREATE_DATE,'month')
8 ;

TO_CHAR SUM(T_PPV_ORDER.PRICE)
------- ----------------------
03/2004 4500
04/2004 5400
05/2004 1400
06/2004 1500
07/2004 1500

This query dispaly the total price which payed for a program per month.

Now i need to add a new coulmn to the query: Cumulative revenue.

The result should be as the following:

TO_CHAR SUM(T_PPV_ORDER.PRICE) Cumuilative Revenue
------- ---------------------- -------------------
03/2004 4500 4500
04/2004 5400 9900
05/2004 1400 11300
06/2004 1500 12800
07/2004 1500 14300

The cumultive revenue displays the cumulative total price per the month.

How can i do it?
Is there a specific analitic function which treats this issue?

Thanks in advance,
Nir

[slimdave]

Yes, analytic functions do this. They operate on a "window" of data within the result set.

http://download-west.oracle.com/docs...htm#sthref1047

For example, the expression ...

Sum(T_PPV_ORDER.PRICE) Over 
   (Rows Between Unbounded Preceeding And Current Row) cume_price
...

[tamilselvan]

Here is an example:

SQL> select * from t1 ;

    ORD_ID ORD_DATE                ORD_AMT
---------- -------------------- ----------
         8 15-dec-2004 09:56:48        200
         1 15-dec-2004 09:56:58         25
         2 15-dec-2004 09:57:30         50
         9 15-dec-2004 09:57:39        225
         7 15-dec-2004 09:57:48        175
         5 15-dec-2004 09:58:03        125
         4 15-dec-2004 09:58:25        100
         3 15-dec-2004 09:59:32         75
         6 15-dec-2004 10:11:24        150

9 rows selected.

SQL> get ana
  1  select ord_date, ord_id, ord_amt,
  2         sum(ord_amt) over (
  3         order by ord_id range unbounded preceding) cum_sale
  4* from t1
SQL> /

ORD_DATE                 ORD_ID    ORD_AMT   CUM_SALE
-------------------- ---------- ---------- ----------
15-dec-2004 09:56:58          1         25         25
15-dec-2004 09:57:30          2         50         75
15-dec-2004 09:59:32          3         75        150
15-dec-2004 09:58:25          4        100        250
15-dec-2004 09:58:03          5        125        375
15-dec-2004 10:11:24          6        150        525
15-dec-2004 09:57:48          7        175        700
15-dec-2004 09:56:48          8        200        900
15-dec-2004 09:57:39          9        225       1125

9 rows selected. 

Tamil

[nir_s]

Hi Tamil and slimdave,

Thanks a lot for both of you!
It works perfect!

Best regards,
Nir