sql - Subtract the value of a row from grouped result -

i have table supplier_account has 5 coloumns supplier_account_id(pk),supplier_id(fk),voucher_no,debit , credit. want sum of debit grouped supplier_id , subtract value of credit of rows in voucher_no not null. each subsequent rows value of sum of debit gets reduced. have tried using 'with' clause.

with debitdetails as( select supplier_id,sum(debit) amt supplier_account group supplier_id ) select acs.supplier_id,s.supplier_name,acs.purchase_voucher_no,acs.purchase_voucher_date,dd.amt-acs.credit amount supplier_account acs left join supplier s on acs.supplier_id=s.supplier_id left join debitdetails dd on acs.supplier_id=dd.supplier_id voucher_no not null 

but here debit value same rows. after subtraction in first row want result in second row , subtract next credit value that.

i know possible using temporary tables. problem cannot use temporary tables because procedure used generate reports using jasper reports.

what need implementation of running total. easiest way of window function:

with debitdetails as( select id,sum(debit) amt suppliers group id ) select s.id, purchase_voucher_no, dd.amt, s.credit, dd.amt - sum(s.credit) on (partition s.id order purchase_voucher_no asc) suppliers s left join debitdetails dd on s.id=dd.id order s.id, purchase_voucher_no 

sql fiddle

results:

| id | purchase_voucher_no | amt | credit | ?column? | |----|---------------------|-----|--------|----------| |  1 |                   1 |  43 |      5 |       38 | |  1 |                   2 |  43 |     18 |       20 | |  1 |                   3 |  43 |      8 |       12 | |  2 |                   4 |  60 |      5 |       55 | |  2 |                   5 |  60 |     15 |       40 | |  2 |                   6 |  60 |     30 |       10 |