SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Merge two table summing a rows

    Suppose I have 2 tables

    Table 1:
    VID score
    1 4
    2 7
    3 1
    4 2

    and

    Table 2
    VID score
    1 3
    2 5
    4 1
    5 2

    I want to combine both of them to get

    Table 3
    VID score
    1 7
    2 12
    3 1
    4 3
    5 2


    Note that the scores for the common VIDs got added up and each VID only shows once in the final table. Is there an sql statement that would do this for me?

    Thanks!
    Last edited by smarty_pockets; Jun 21, 2008 at 02:28. Reason: better formatting

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT VID
         , SUM(score) AS score
      FROM ( SELECT VID, score FROM table1
             UNION ALL
             SELECT VID, score FROM table2
           ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Worked like a charm.


Bookmarks

Posting Permissions

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