CODEWITHSUNDEEP
databaseoracle-databaserelational-databasecalculated-columns
Sunny
Sunny

Reputation: 47

sql calculate multiple records with another table

I have two tables here. What I want is calculate(add) the amount and balance within these two table. But the difficult is that the two account_no are the same(A-102) in transactions table. So how to add amount to balance when transactions.account_id= account.account_no = A-102??

This is transactions table

The account table

What I have done is :

    select account_no, balance + (
                              select t.amount
                              from transactions t
                              where t.account_no = 'A-222')
    from b_account 

    where account_no = 'A-222';

This method works only A-305 and A-222. If I write like this, it won't work..

select account_no, balance + (
                              select t.amount
                              from transactions t
                              where t.account_no = (
                                                    select t.account_no 
                                                    from b_account ba, transactions t
                                                    where ba.account_no = t.account_no
                                                    )      
                              )
from b_account 
where account_no = (select t.account_no 
                    from b_account ba, transactions t
                    where ba.account_no = t.account_no);

Any help thanks in advanced!!

Upvotes: 0

Views: 150

Answers (1)

rs.
rs.

Reputation: 27427

You can group by account number and sum all amounts and then join the result to the account table. Try this

with cte as
(
 select account_no, SUM(t.amount) amount
 from transactions t
 --where t.account_no = 'A-222'
 group by account_no
)

Select a.account_no, balance + coalesce(b.amount,0) new_balance
from b_account a
left outer join cte b on a.account_no = b.account_no
--where a.account_no = 'A-222'

Upvotes: 1

Related Questions