CODEWITHSUNDEEP
sqloracle-database
Thepallav_abhi
Thepallav_abhi

Reputation: 105

How to get the sum of row in oracle?

I have a written a query which is giving following output

But my actual need is something different

TRANSACTION_DATE    DETAILS          LEAVE_CREDITED LEAVE_DEBITED
29-Sep-2012      Sep-2012-Sep-2012        0.11  
01-Oct-2012      Oct-2012-Dec-2012        2.5   
01-Jan-2013      Jan-2013-Mar-2013        2.5   
31-Mar-2013      LAPSE - 540007                        1.9
01-Apr-2013      Apr-2013-Jun-2013        2.5   
30-Apr-2013      Lev_102935703                        0.11

There should be a 5th column such that

Its value should be LASTBALANCE+(Leave_Credited)-(Leave_Debited) In this case

BALANCE
0.11-0=         0.11
0.11+(2.5-0)=   2.61
2.61+(2.5-0)=   5.11
5.11+(0-1.9)=   3.02

Please help.

My query is something like

SELECT TRUNC(NVL(C.UPDATED_DATE, C.CREATED_DATE)) TRANSACTION_DATE,
       TO_CHAR(C.SERVICE_START_DATE, 'Mon-YYYY') || '-' ||
       TO_CHAR(C.SERVICE_END_DATE, 'Mon-YYYY') Details,
       C.LEAVE_CREDITED,
       NULL LEAVE_DEBITED
  FROM LEAVE.GES_LEV_CREDIT_DETAILS C, LEAVE.GES_LEV_CREDIT_MASTER CM
 WHERE C.LEV_CREDIT_ID = CM.LEV_CREDIT_ID
   AND C.PERSON_ID = 12345
   AND CM.COUNTRY_LEAVE_TYPE_ID = 5225
     AND c.leave_credited<>0
UNION
SELECT TRUNC(NVL(d.UPDATED_DATE, d.CREATED_DATE)) TRANSACTION_DATE,
       d.reference,
       NULL,
       d.no_of_days LEAVE_DEBITED
  FROM LEAVE.GES_LEV_CREDIT_DETAILS C,
       LEAVE.GES_LEV_CREDIT_MASTER  CM,
       leave.ges_lev_debit_req_dtls       D
 WHERE C.LEV_CREDIT_ID = CM.LEV_CREDIT_ID
   AND C.LEV_CREDIT_DETAIL_ID = D.LEV_CREDIT_DETL_ID
   AND C.PERSON_ID = 12345
   AND CM.COUNTRY_LEAVE_TYPE_ID = 5225

Upvotes: 2

Views: 101

Answers (2)

MontyPython
MontyPython

Reputation: 3012

I am using Oracle.

You can use sum() over (order by transaction_date) to get a running total - which will handle the lapsed leaves etc. ->

Output:

enter image description here

Script:

select to_date(transaction_date) transaction_date, details, 
leave_credited, leave_debited, 
sum(leave_credited - leave_debited) over (order by to_date(transaction_date) asc) final_balance
from
  (select '29-Sep-2012' transaction_date,
    'Sep-2012-Sep-2012' details,
    0.11 leave_credited,
    0 leave_debited
  from dual
  union all
  select '01-Oct-2012' , 'Oct-2012-Dec-2012' , 2.5 , 0 from dual
  union all
  select '01-Jan-2013' , 'Jan-2013-Mar-2013' , 2.5 , 0 from dual
  union all
  select '31-Mar-2013' , 'LAPSE - 540007' , 0, 1.9 from dual
  union all
  select '01-apr-2013' , 'apr-2013-jun-2013' , 2.5, 0 from dual
  union all
  select '30-Apr-2013' , 'Lev_102935703' , 0, 0.11 from dual
  );

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

5.11+(0-1.9)= 3.02

Shouldn't it be 3.21.

Use Analytic SUM() OVER() for both credited and debited columns and then take the difference of them.

Let's see a working test case, I have built your table using WITH clause, in reality you just need to use your table instead of DATA:

SQL> WITH DATA AS(
  2  SELECT to_date('29-Sep-2012', 'dd-Mon-yyyy') TRANSACTION_DATE, 0.11 LEAVE_CREDITED, NULL LEAVE_DEBITED FROM dual UNION ALL
  3  SELECT to_date('01-Oct-2012', 'dd-Mon-yyyy') TRANSACTION_DATE, 2.5  LEAVE_CREDITED, NULL LEAVE_DEBITED FROM dual UNION ALL
  4  SELECT to_date('01-Jan-2013', 'dd-Mon-yyyy') TRANSACTION_DATE, 2.5  LEAVE_CREDITED, NULL LEAVE_DEBITED FROM dual UNION ALL
  5  SELECT to_date('31-Mar-2013', 'dd-Mon-yyyy') TRANSACTION_DATE, NULL LEAVE_CREDITED, 1.9 LEAVE_DEBITED FROM dual UNION ALL
  6  SELECT to_date('01-Apr-2013', 'dd-Mon-yyyy') TRANSACTION_DATE, 2.5   LEAVE_CREDITED, NULL LEAVE_DEBITED FROM dual UNION ALL
  7  SELECT to_date('30-Apr-2013', 'dd-Mon-yyyy') TRANSACTION_DATE, null LEAVE_CREDITED, 0.11 LEAVE_DEBITED FROM dual
  8  )
  9  SELECT t.*,
 10    SUM(NVL(leave_credited,0)) OVER(ORDER BY TRANSACTION_DATE)
 11     -
 12    SUM(NVL(LEAVE_DEBITED,0)) OVER(ORDER BY TRANSACTION_DATE) LASTBALANCE
 13  FROM DATA t
 14  /

TRANSACTI LEAVE_CREDITED LEAVE_DEBITED LASTBALANCE
--------- -------------- ------------- -----------
29-SEP-12            .11                       .11
01-OCT-12            2.5                      2.61
01-JAN-13            2.5                      5.11
31-MAR-13                          1.9        3.21
01-APR-13            2.5                      5.71
30-APR-13                          .11         5.6

6 rows selected.

SQL>

Your query would look like:

SELECT t.*,
  SUM(NVL(leave_credited,0)) OVER(ORDER BY TRANSACTION_DATE) 
   - 
  SUM(NVL(LEAVE_DEBITED,0)) OVER(ORDER BY TRANSACTION_DATE) LASTBALANCE
FROM table_name t                                                  
/

Upvotes: 2

Related Questions