RE: Sum of Previous Record

From: Pardee, Roy E <roy.e.pardee_at_lmco.com>
Date: Wed, 04 Jun 2003 07:59:44 -0800
Message-ID: <F001.005AA06A.20030604075944@fatcity.com>

Depending on the structure of your table, you can also use plain SQL with a self-join, similar to:

      select v1.cust_id
            , v1.order_date
            , v1.order_total
            , sum(v2.order_total) cumulative_total
      from orders v1
         , orders v2
      where v1.cust_id = v2.cust_id AND
            v2.order_date <= v1.order_date
      group by v1.cust_id, v1.order_date, v1.order_total ;

Cheers,

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----
Sent: Wednesday, June 04, 2003 8:05 AM
To: Multiple recipients of list ORACLE-L

Walid

You can use SUM as an analytic function like this: -

SQL> select credit, debit, sum(credit + debit) over (order by rowid) from foo;

    CREDIT DEBIT SUM(CREDIT+DEBIT)OVER(ORDERBYROWID)

---------- ---------- -----------------------------------
         3          0                                   3
         0         -1                                   2
         1          0                                   3

Obviously, you will need to order by something more sensible than the rowid (perhaps a timestamp).

> -----Original Message-----
> From: Walid Alkaakati [mailto:Walid.Alkaakati_at_astrolabeit.com]
> Sent: 04 June 2003 14:10
> To: Multiple recipients of list ORACLE-L
> Subject: Sum of Previous Record
>
>
>
>
> Hi list ,
>
> Can you help me please .
>
> I have a report that show data as follows :
>
> debit credit balance
> 3 0 -3
> 0 -1 2
> 1 0 3
>
> Is their a way to get balance without using a separet query in a
> formula column,i .e i need the value of the previous
> record in the same repeating frame.
>
> Thanks
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Walid Alkaakati
> INET: Walid.Alkaakati_at_astrolabeit.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

---

This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at email.helpdesk_at_hays.com
Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays.  

A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.

---

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Lord, David - CSG
  INET: david.lord_at_hays.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: roy.e.pardee_at_lmco.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 04 2003 - 10:59:44 CDT