John - 1 year ago 50
SQL Question

# How can I get the BALANCE for every row using the query below

I want to get the balance for every Debit and Credit then calculate the balance per row..

``````SELECT SUM(IF(s.AMT>0 AND s.SLE_CODE=11,s.AMT,0)) AS DEBIT,
SUM(IF(s.AMT<0 AND s.SLE_CODE=11,s.AMT,0)) * -1 AS CREDIT
FROM
sldtl AS s
LEFT JOIN
transtype
ON
transtype.TransTypeID = s.TR_CODE
WHERE
s.SL_BRCODE= 1 AND s.SL_CLIENTID= 267 AND s.SLC_CODE= 13 AND s.SLT_CODE= 15 AND
s.REF_NO= s.REF_NO AND s.TR_DATE >= (SELECT s.TR_DATE FROM sldtl AS s ORDER BY s.TR_DATE DESC LIMIT 1)-INTERVAL 6 MONTH
GROUP BY
s.TR_DATE DESC, s.TR_CODE, s.TR_CTLNO
``````

needed result

`````` +-----------+--------------+---------------+
|    DEBIT  |    CREDIT    |    BALANCE    |
+-----------+--------------+---------------+
|      0.00 |       700.00 |       -700.00 |
|      0.00 |       700.00 |      -1400.00 |
|    400.00 |         0.00 |      -1000.00 |
|   2000.00 |       500.00 |        500.00 |
+-----------+--------------+---------------+
``````

Answer Source

Let's consider the simplified version of your problem:

A table containing only `debit` and `credit` columns. And you want to calculate the `balance` column:

balanceTable:

`````` debit    credit
0        700
0        700
400          0
2000        500

SELECT
debit,
credit,
@balance := @balance + debit - credit AS balance
FROM balancetable, (SELECT @balance := 0) var;
``````

SQL FIDDLE DEMO

So in your case it would be like:

``````SELECT
t.DEBIT,
t.CREDIT,
@balance := @balance + t.DEBIT - t.CREDIT AS BALANCE
FROM
(
SELECT SUM(IF(s.AMT>0 AND s.SLE_CODE=11,s.AMT,0)) AS DEBIT,
SUM(IF(s.AMT<0 AND s.SLE_CODE=11,s.AMT,0)) * -1 AS CREDIT
FROM
sldtl AS s
LEFT JOIN
transtype
ON
transtype.TransTypeID = s.TR_CODE
WHERE
s.SL_BRCODE= 1 AND s.SL_CLIENTID= 267 AND s.SLC_CODE= 13 AND s.SLT_CODE= 15 AND
s.REF_NO= s.REF_NO AND s.TR_DATE >= (SELECT s.TR_DATE FROM sldtl AS s ORDER BY s.TR_DATE DESC LIMIT 1)-INTERVAL 6 MONTH
GROUP BY
s.TR_DATE DESC, s.TR_CODE, s.TR_CTLNO
) AS t , (SELECT @balance := 0) var;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download