how to calculate from each row value in sql

Question

I have a table named general_ledger from which I need to show dr_amount, cr_amount and the balance between them as running_balance. That's why I have written a query that is given below. But I am getting the result of each query like the balance only of current row. But I need to produce the result with the remaining balance. Suppose First row dr_balance is 20000 and cr_balance is 5000 and remaining balance is 15000. In second row only cr_balance is 5000. Now the result should be 10000 with the deduction but my result is -5000. I have no idea how to fix this. Can anyone please help me on this? I need your help very much. Here is my query given below :

    SELECT                    '' AS cost_center_id
                                        , '' AS cost_center_name
                                        , '' AS office_code
                                        , CONVERT('2013-02-01',DATETIME) AS transaction_date
                                        , '' AS accounts_head_id
                                        , '' AS account_name
                                        , '' AS opposite_accounts_head_id
                                        , '' AS opposite_account_name
                                        , 'Opening Balance' AS particulars
                                        , tempOpeningBalance.dr_amount
                                        , tempOpeningBalance.cr_amount
                                        , '' AS voucher_no
                                        , '' AS vin
                                        FROM (SELECT IFNULL(mcoa.account_code,'1101010101100321') AS account_code
                                                , IFNULL(mcoa.account_name,'Cash') AS account_name
                                                , IFNULL(mcoa.account_type,'ASSET') AS accountType
                                                , CAST(IFNULL(SUM(IFNULL(maingl.dr_balance,0)),0) AS DECIMAL(27,5)) AS dr_amount
                                                , CAST(IFNULL(SUM(IFNULL(maingl.cr_balance,0)),0) AS DECIMAL(27,5)) AS cr_amount
                                                FROM master_chart_of_accounts AS mcoa
                                                INNER JOIN chart_of_accounts AS coa ON (mcoa.id = coa.master_chart_of_accounts_id AND mcoa.id = 80)
                                                LEFT JOIN general_ledger AS maingl ON (coa.id = maingl.accounts_head_id AND coa.account_code='1101010101100321')
                                                INNER JOIN
                                                (   SELECT  gl.accounts_head_id, MAX(gl.gl_id) AS max_gl_id, gl.office_code, gl.office_type, gl.country_id,gl.cost_center_id
                                                       FROM     general_ledger AS gl
                            -- INNER JOIN voucher_info AS vi ON (gl.voucher_info_id = vi.id)
                                                        -- WHERE  vi.posting_date < '2013-02-01' AND
                                                        WHERE gl.transaction_date < '2013-02-01' AND
                                                          gl.cost_center_id IN ('BI0000000000000000000001') AND
                                                          gl.country_id IN (1) AND
                                                          gl.office_code IN ('UG500013') AND
                                                          1=1
                                                GROUP BY gl.accounts_head_id, gl.office_code, gl.office_type, gl.country_id,gl.cost_center_id
                                                ORDER BY gl.accounts_head_id
                                                ) AS tmpgl
                                                ON (    maingl.office_code = tmpgl.office_code
                                                AND maingl.office_type = tmpgl.office_type
                                                AND maingl.accounts_head_id = tmpgl.accounts_head_id
                                                AND maingl.country_id = tmpgl.country_id
                                                AND maingl.cost_center_id = tmpgl.cost_center_id
                                                AND maingl.gl_id = tmpgl.max_gl_id
                                                   )
                                                 WHERE  mcoa.account_status_id = 1 AND
                                                    coa.account_status_id = 1
                                             ) AS tempOpeningBalance

UNION

SELECT      vi.cost_center_id
      , cc.center_name AS cost_center_name
      , gl.office_code
      , vi.posting_date AS transaction_date
      , vd.accounts_head_id
      , (SELECT chart_of_accounts.account_name FROM chart_of_accounts WHERE chart_of_accounts.id = vd.accounts_head_id) AS account_name
      , vd.opposite_accounts_head_id
      , (SELECT chart_of_accounts.account_name FROM chart_of_accounts WHERE chart_of_accounts.id = vd.opposite_accounts_head_id) AS opposite_account_name
      , vd.particulars
      , gl.dr_amount AS dr_amount -- here to check
      , gl.cr_amount AS cr_amount
      , vi.voucher_no
      , vi.vin
FROM general_ledger AS gl
INNER JOIN voucher_info AS vi
 ON (gl.voucher_info_id = vi.id)
INNER JOIN cost_center AS cc
 ON (vi.cost_center_id = cc.id)
INNER JOIN voucher_details AS vd
 ON (vi.id = vd.voucher_info_id)
INNER JOIN chart_of_accounts AS coa
 ON (vd.accounts_head_id = coa.id)

WHERE   vi.posting_date BETWEEN '2013-02-01' AND'2013-02-28'
    AND vi.voucher_status_id = 3
    AND vd.status_id = 1
    AND vi.office_code = 'UG500063'
    AND coa.account_code='1101010101100321'
    AND coa.cost_center_id = 'BI0000000000000000000001'
ORDER BY   cost_center_name
     , office_code
     , transaction_date;

Answer 1

Use a variable like this

SET @running_balance=0;
SELECT dr_amount AS dr_amount 
      , cr_amount AS cr_amount
      , @running_balance := (@running_balance + dr_amount - cr_amount)
FROM general_ledger