I have two tables tb1 and tb2. I would like to sum up field values from two tables. How to do this using a SQL statement.

tb1
Name Salary
Bob 1000
Tom 2000
John 3000
Winson 4000

tb2
Name Bonus
Bob 100
Tom 200


I would like to get the following result
Name Total
Bob 1100
Tom 2200
John 3000
Winson 4000

I have tried with the following statement but no luck.

try a join.... the where type will only find exact matches, whereas a join is more flexible

select tb1.name, (tb1.salary+tb2.bonus) as remuneration from tb1
left join tb2 on tb2.Name=tb1.Name
order by Name

thank you healdem for your resolution

COALESCE, healdem, COALESCE

I'm intrigued how will COALESCE work in this instance?
what would make it a preferable solution to a left join in this instance?

What's 10 + NULL ?

easy 10NULL!

but in this case the left join should cater for the null... admittedly it wouldn't work if tbl2 could have a null value in the bonus, or if tbl1 had a null value... but outside of that I don't see any meaningful advantage in using coalesce

(tb1.salary+tb2.bonus) as remuneration

If bonus was NULL, what would remuneration then be? Would this be the correct thing?

the information in the OP was that tbl1 had the employee name + salary
tbl2 had name + bonus
the sample data indicated that a row only appeared if there was a bonus, there was always a salary in tbl1.

had it been my table design I would have have declared not null and deafult value for both elements...

so you would have a row in the bonus table with a value of 0 ???

BLECCH!!!

you need to take some normalization tutorials

COALESCE used with LEFT OUTER JOIN is very common

Thank you all for your discussions and suggestions.

In my case, everybody (every emplpyee) has salary but may or may not have bonus. The names of employees who have no bonus will not appear in tb2.

I wonder that under this circumstance, healdem's solution is correct or not?

When should I use LEFT outer join,

LEFT JOIN with Coalesce

when you want

• every row from one table
• with or without matching row(s) from the other table

Thanks for your reply. Hwever, I am not clear about why having 'with Coalesce'. What does it mean. I understand 'LEFT JOIN' but not 'LEFT JOIN with Coalesce'


Healdem's solution is correct for my case right?

let me explain why COALESCE is important

in a LEFT OUTER JOIN, you will have some salary rows where there is no matching bonus row

correct so far?

so for those rows, if you use salary+bonus, and bonus is NULL, then adding them together, salary+bonus, gives NULL

however, if you use salary+COALESCE(bonus,0) instead, if bonus is missing, then COALESCE(bonus,0) yields 0, so salary+0 gives salary

that's why COALESCE is important