cumulative aggregation in a query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: cumulative aggregation in a query

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    cumulative aggregation in a query

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...
    Code:
    Sum(T_PPV_ORDER.PRICE) Over 
       (Rows Between Unbounded Preceeding And Current Row) cume_price
    ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Here is an example:
    PHP Code:
    SQLselect 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
    .

    SQLget ana
      1  select ord_date
    ord_idord_amt,
      
    2         sum(ord_amtover (
      
    3         order by ord_id range unbounded precedingcum_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
    Last edited by tamilselvan; 12-15-2004 at 01:58 PM.

  4. #4
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi Tamil and slimdave,

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

    Best regards,
    Nir

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width