calculating accounts balance from debit and credit table

Question

I have a accounts table like this

+-----------+--------------+ | fld_id| name | +-----------+--------------+ | 1 | Bank1| | 2 | Bank2| | 4 | Bank3| +-----------+--------------+

Revenue Income Table Like this

+-----------+--------------+---------------+-------------+ | fld_id | fld_type | fld_account id| fld_amount | +-----------+--------------+---------------+-------------+ | 1 | Salry| 1 | 400 | | 2 | Rent | 2 | 500 | | 4 | Others | 1 | 1000 | +-----------+--------------+---------------+-------------+

Payment Table Like This

+-----------+--------------+---------------+-------------+ | fld_id | fld_type | fld_account id| fld_amount | +-----------+--------------+---------------+-------------+ | 1 | Food | 2 | 200 | | 2 | Entertain | 2 | 300 | | 4 | Transport | 1 | 400 | +-----------+--------------+---------------+-------------+

I want a final balance table for accounts with sum of income, expence and balance like This Table --

+-----------+--------------+---------------+-------------+ | account | Income | Expence | Balance | +-----------+--------------+---------------+-------------+ | Bank1 | 1400 | 400 | 1000 | | Bank2 | 500 | 500 | 0 | | Bank3 | 0 | 0 | 0 | +-----------+--------------+---------------+-------------+

So far I write this query and getting income and expense but did't find any way to calculate balance, my query and result is --query

SELECT fld_account as account, Income, Expense from tbl_accounts LEFT JOIN (SELECT fld_account_id, SUM(fld_amount) as Income FROM tbl_revenue tr GROUP BY tr.fld_account_id) tc on fld_id=tc.fld_account_id left JOIN (SELECT fld_account_id, SUM(fld_amount) as Expense FROM tbl_payment tp GROUP BY tp.fld_account_id) td on fld_id=td.fld_account_id

and the result is like below --

+-----------+--------------+---------------+ | account | Income | Expense | +-----------+--------------+---------------+ | Bank1 | 1400 | 400 | | Bank2 | 500 | 500 | | Bank3 | Null | Null | +-----------+--------------+---------------+

How can I calculate balance form payment and revenue table and join it with my account table? Any help is very appreciated.

Gordon Linoff · Accepted Answer · 2018-03-05 02:39:10Z

Just use coalesce():

SELECT fld_account as account, COALESCE(Income, 0) as Income, COALESCE(Expense, 0) as Expense, ( COALESCE(Income, 0) - COALESCE(Expense, 0) ) as balance FROM tbl_accounts LEFT JOIN (SELECT fld_account_id, SUM(fld_amount) as Income FROM tbl_revenue tr GROUP BY tr.fld_account_id ) tc ON fld_id = tc.fld_account_id LEFT JOIN (SELECT fld_account_id, SUM(fld_amount) as Expense FROM tbl_payment tp GROUP BY tp.fld_account_id ) td ON fld_id = td.fld_account_id;

COALESCE() is the ANSI-standard function that returns the first non-NULL argument.

One thing will you please explain what coalesec() actually did?

Collectives™ on Stack Overflow

calculating accounts balance from debit and credit table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related