使用mysql中的WHERE子句显示运行余额但仅显示指定的行

Question

I have two table named nca and issue_details describes below with values:

Table: nca
+--------+--------+------------+-------------+--------------+
| nca_id | nca_no | issue_date | nca_amount  | account_type |
+--------+--------+------------+-------------+--------------+
|      1 | 001    | 2015-01-11 | 19264373.46 | ROP          |
|      2 | 002    | 2015-01-11 |     1752000 | ROP          |
|      3 | 001    | 2015-01-11 |      200000 | DBP-TRUST    |
|      4 | 002    | 2015-01-11 |     3000000 | DBP-TRUST    |
+--------+--------+------------+-------------+--------------+

Table: issue_details
+----------+------------+----------+----------+-------+
| check_no | issue_date | issue_amount | account_type | 
+----------+------------+--------------+----------+---+
|        1 | 2015-01-11 |         2800 | ROP          | 
|        2 | 2015-01-11 |         2800 | ROP          |    
|        3 | 2015-01-11 |      1999.17 | ROP          |    
|        5 | 2015-01-11 |       200000 | DBP-TRUST    |  
|        6 | 2015-01-11 |       200000 | DBP-TRUST    |  
+----------+------------+--------------+----------+---+

I want to display its running balance subtracted to each issue_amount from issue_details table to the sum of nca_amount from nca table having the same issue_date and account_type. In my two tables, heres what I want to happen:

First, Sum up all the nca_amount where issue_date = 2015-01-11 and account_type = ROP.

Second, Subtract the result from the first step to each issue_amount from the issue_details table where issue_date = 2015-01-11 and account_type = ROP. Shown below :

Sum of of this two rows is 2,101,6373.46

+--------+--------+------------+-------------+--------------+
| nca_id | nca_no | issue_date | nca_amount  | account_type |
+--------+--------+------------+-------------+--------------+
|      1 | 001    | 2015-01-11 | 19264373.46 | ROP          |
|      2 | 002    | 2015-01-11 |     1752000 | ROP          |

Then subtract to each issue_amount from issue_details table where issue_date = 2015-01-11 and account_type = ROP would be :

21,016,373.46 - 2,800 = 21,013,573.46,
21,013,573.46 - 2,800 = 21,010,773.46,
21,010,773.46 - 1,999.17 = 21,008,774.29

Fortunately, someone gave me this code to display the running balance and I added a where clause to display only the data where account_type = 'ROP' and issue_date = '2015-01-10' :

SELECT r.*,
 (@tot := @tot - issue_amount) as bank_balance
 FROM (SELECT @tot := SUM(nca_amount) as nca_total FROM nca
    WHERE account_type = 'ROP' AND 
    issue_date = '2015-01-11'
  )
vars CROSS JOIN issue_details r
ORDER BY r.issue_date, r.check_no;

and heres what happen :

+----------+------------+--------------+--------------+--------------+
| check_no | issue_date | issue_amount | account_type | bank_balance |
+----------+------------+--------------+--------------+---------------
|        1 | 2015-01-11 |         2800 | ROP          |  21013573.46 | 
|        2 | 2015-01-11 |         2800 | ROP          |  21010773.46 |
|        3 | 2015-01-11 |      1999.17 | ROP          |  21008774.29 |
|        5 | 2015-01-11 |       200000 | DBP-TRUST    |  20808774.29 |  -- wrong
|        6 | 2015-01-11 |       200000 | DBP-TRUST    |  20608774.29 |  -- wrong
+----------+------------+--------------+--------------+--------------+

heres what I want to display :

+----------+------------+--------------+--------------+--------------+
| check_no | issue_date | issue_amount | account_type | bank_balance |
+----------+------------+--------------+--------------+---------------
|        1 | 2015-01-11 |         2800 | ROP          |  21013573.46 | 
|        2 | 2015-01-11 |         2800 | ROP          |  21010773.46 |
|        3 | 2015-01-11 |      1999.17 | ROP          |  21008774.29 |
+----------+------------+--------------+--------------+--------------+

Only the rows having the field of issue_date = '2015-01-11' and account_type = 'ROP'. Can anyone help me re- code that query above to display that result ? Thanks.

Answer 1

Use can use having or where in your query. Please check this query
(1) Where :

SELECT r.*,
 (@tot := @tot - issue_amount) as bank_balance
 FROM (SELECT @tot := SUM(nca_amount) as nca_total FROM nca
    WHERE account_type = 'ROP' AND 
    issue_date = '2015-01-11'
  )
vars CROSS JOIN issue_details r
WHERE r.account_type != 'DBP-TRUST'
ORDER BY r.issue_date, r.check_no

(2) Having :

SELECT r.*,
 (@tot := @tot - issue_amount) as bank_balance
 FROM (SELECT @tot := SUM(nca_amount) as nca_total FROM nca
    WHERE account_type = 'ROP' AND 
    issue_date = '2015-01-11'
  )
vars CROSS JOIN issue_details r
HAVING r.account_type != 'DBP-TRUST'
ORDER BY r.issue_date, r.check_no