Joseph Goh Joseph Goh - 7 months ago 14
SQL Question

MySQL Vertical Horizontal Sum and Vertical Sum At same time

I have 2 tables which consist a set of data as below and i want to get the result as in the result which will have a calculation in field

balance
:

I am stuck on the
balance
field, how do i get the
balance
running?

tblIn

in_date | code | in_qty
-----------|---------|---------
2016-04-01 | aaa | 100
2016-04-03 | aaa | 200
2016-04-06 | aaa | 400


tblOut

out_date | code | out_qty
-----------|---------|---------
2016-04-02 | aaa | 100
2016-04-08 | aaa | 400


RESULT

date | code | in_qty | out_qty | balance
-----------|---------|----------|----------|---------
2016-04-01 | aaa | 100 | 0 | 100
2016-04-02 | aaa | 0 | 100 | 0
2016-04-03 | aaa | 200 | 0 | 200
2016-04-06 | aaa | 400 | 0 | 600
2016-04-08 | aaa | 0 | 400 | 200


QUERY

SELECT
t.date,
t. CODE,
t.in_qty,
t.out_qty
FROM
(
SELECT
date,
in_qty,
0 AS out_qty
FROM tblIn
UNION ALL
SELECT
date,
0 AS in_qty,
out_qty
FROM tblOut
) t
ORDER BY date ASC

Answer

You can try this query:

SELECT 
t.*,
@prevBalance := (t.in_qty - t.out_qty) + IFNULL(@prevBalance,0) AS balance
FROM 
(
    SELECT 
        in_date date,
        code,
        in_qty,
        0 AS out_qty
    FROM tblin

    UNION 

    SELECT 
        out_date,
        code,
        0,
        out_qty
    FROM tblout
) t , (SELECT @prevBalance := NULL) var
ORDER BY t.date;

SQL FIDDLE DEMO

Note:

If you initialize @prevBalance variable with zero then IFNULL(@prevBalance,0) is no longer needed.

So if you use this:

(SELECT @prevBalance := 0) var

then change the balance column like below:

@prevBalance := (t.in_qty - t.out_qty) + @prevBalance

Comments