Adding columns if available

Question

Here I have 2 tables 1st table consist of 10 rows of acnumber column and 10 rows of opening_balance column. 2nd table consists of 3 of those acnumber in a column having made transaction several times mentioned in transaction_type column as 'Deposit' or 'Withdrawal' and transaction_amount column .

so I have to select all acnumber and their deposits made i.e opening balance+deposits.If no transaction available in table 2 then add opening balance and 0. I tried something like this

select a1.acnumber,a1.opening_balance+if (a1.acnumber not in (a2.acnumber),0,sum(a2.transaction_amount))
from account a1 left join trandetails a2
on a1.acnumber=a2.acnumber
where a2.transaction_type like'Deposit'
group by a2.acnumber;

But it returns only acnumber mentioned in both tables.Any suggestions???

Gordon Linoff · Accepted Answer · 2014-02-17 16:15:58Z

1

You need several changes to the query. The where is undoing the left outer join for instance. You should move that condition to the on clause. Also, the group by columns should be the columns in the select that are not in aggregation functions.

So, try this

select a1.acnumber, a1.opening_balance + coalesce(sum(a2.transaction_amount), 0)
from account a1 left join
     trandetails a2
     on a1.acnumber = a2.acnumber and
        a2.transaction_type like 'Deposit'
group by a1.acnumber, a1.opening_balance;

edited Feb 17, 2014 at 16:15

answered Feb 16, 2014 at 18:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding columns if available

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related