CODEWITHSUNDEEP
phpmysql
Sharoon Ck
Sharoon Ck

Reputation: 778

how to select data from two table and sum the balance from debit and credit columns (php)

I have two tables

credit table

id     date      credit
1    4-1-2019     300
2   10-1-2019     500
3   10-2-2019     300

debit table

id     date      debit
1    2-1-2019     400
2   6-1-2019     600
3   2-2-2019     300

I want to get a table which to merge those data and show the balance, ordered by the date

somthing like this

id     date      debit  credit  balance
1    2-1-2019     400            400
1    4-1-2019             300    100
2    6-1-2019     600            700
2   10-1-2019             500    200
3    2-2-2019     300            500
3   10-2-2019             300    200

UPDATE

I am using @Gordon Linoff code 

select b.*,
   (@b := @b + coalesce(debit, 0) - coalesce(credit, 0)) as balance from (select id, date, debit, credit
  from ((select id, date, debit, "0" as credit
         from debit
        ) union all
        (select id, date, "0" as debit, credit
         from credit 
        )
       ) b
  order by date
 ) b CROSS join
 (select @b := 0) params

it working but i geting output like this table

id     date      debit  credit  balance
1    2-1-2019     400            400
2    6-1-2019     600            1000
3    2-2-2019     300            1300
1    4-1-2019             300    1000
2   10-1-2019             500    500
3   10-2-2019             300    200

I need to sort whole data by date here as you can see first sorted data of debit table then data of credit table

Upvotes: 2

Views: 1052

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can combine the tables using union all. In MySQL 8+ you can calculate the last column using cumulative sums:

select id, date, debit, credit,
       sum( coalesce(debit, 0) - coalesce(credit, 0) ) over (order by date) as balance
from ((select id, date, debit, null as credit
       from debit
      ) union all
      (select id, date, null as debit, credit
       from credit 
      )
     ) b
order by date;

In earlier versions, you can use variables for the calculation:

select b.*,
       (@b := @b + coalesce(debit, 0) - coalesce(credit, 0)) as balance
from (select id, date, debit, credit
      from ((select id, date, debit, null as credit
             from debit
            ) union all
            (select id, date, null as debit, credit
             from credit 
            )
           ) b
      order by date
     ) b cross join
     (select @b := 0) params
order by date;

Upvotes: 4

Related Questions