running balance debit credit column in oracle query-CodePudding

I want the running balance in my query. I had wrote the query. May be i am mistaking any where please let me know.

SELECT    
            ACC_VMAST.VM_DATE,
            ACC_VDET.CHEQUE,
            ACC_VMAST.NARRATION,
            ACC_VDET.DEBIT, 
            ACC_VDET.CREDIT,
            sum(nvl(ACC_VDET.DEBIT,0) - nvl(ACC_VDET.CREDIT,0) )
            over (order by ACC_VMAST.VM_DATE , ACC_VDET.DEBIT ) running_bal
FROM        ACC_VMAST,
            ACC_VDET,
            ACC_COA
WHERE       ACC_VMAST.VM_PK=ACC_VDET.VM_PK
AND         ACC_COA.COA_PK=ACC_VDET.COA_PK
AND         ACC_VMAST.POST_BY IS NOT NULL 
AND         ACC_VMAST.CANCEL_STATUS IS NULL
AND         ACC_VMAST.VM_DATE BETWEEN '07/06/2021' AND '07/07/2021'
AND         ACC_VDET.COA_PK= '303'
ORDER BY ACC_VMAST.VM_DATE , ACC_VDET.DEBIT;

CodePudding user response：

You need to add a row range with "rows between unbounded preceding and current row".

sum(nvl(ACC_VDET.DEBIT,0) - nvl(ACC_VDET.CREDIT,0) )
        over (order by ACC_VMAST.VM_DATE , ACC_VDET.DEBIT rows between unbounded preceding and current row ) running_bal

CodePudding user response：

If you have rows that have the same values for the ORDER BY clause then when you SUM the values then all the rows with the same ORDER BY value will be grouped together and totalled.

To prevent that, you can add the ROWNUM pseudo-column to the ORDER BY clause of the analytic function so that there will not be any ties:

SELECT m.VM_DATE,
       d.CHEQUE,
       m.NARRATION,
       d.DEBIT, 
       d.CREDIT,
       SUM( COALESCE(d.DEBIT,0) - COALESCE(d.CREDIT,0) )
         OVER ( ORDER BY m.VM_DATE, d.DEBIT, ROWNUM ) AS running_bal
FROM   ACC_VMAST m
       INNER JOIN ACC_VDET d
       ON (m.VM_PK = d.VM_PK)
       INNER JOIN ACC_COA c
       ON (c.COA_PK = d.COA_PK)
WHERE  m.POST_BY IS NOT NULL 
AND    m.CANCEL_STATUS IS NULL
AND    m.VM_DATE BETWEEN DATE '2021-07-06' AND DATE '2021-07-07'
AND    d.COA_PK = '303'
ORDER BY
       m.VM_DATE,
       d.DEBIT;