Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: how to query from two tables (sum up field values)

    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.
    Code:
    SELEC tb1.Name, SUM(tb1.Salary + tb2.Bonus) Total From tb1, tb2 WHERE tb1.Name = tb2.Name group by tb1.Name order by Total
    Last edited by cy163; 10-09-08 at 00:01.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    thank you healdem for your resolution

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    COALESCE, healdem, COALESCE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm intrigued how will COALESCE work in this instance?
    what would make it a preferable solution to a left join in this instance?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's 10 + NULL ?
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    (tb1.salary+tb2.bonus) as remuneration

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

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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...
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2007
    Posts
    130
    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,

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    LEFT JOIN with Coalesce
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cy163
    When should I use LEFT outer join,
    when you want
    • every row from one table
    • with or without matching row(s) from the other table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2007
    Posts
    130
    Quote Originally Posted by georgev
    LEFT JOIN with Coalesce
    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?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •