Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    19

    Unanswered: how to join the table?

    I have two table as follow

    TableA
    PID, Amount1
    1, 10
    1, 20
    2, 10
    2, 10
    3, 30

    TableB
    PID, Amount2
    1, 20
    1, 50
    2, 30
    2, 20

    SELECT TableA.PID, Sum(TableA.Amount1) AS Amount1OfSum, Sum(TableB.Amount2) AS Amount2OfSum
    FROM TableA left JOIN TableB ON TableA.PID = TableB.PID
    GROUP BY TableA.PID;

    The output is
    PID, Amount1, Amount2
    1, 90, 200
    2, 20, 40
    3, 30,


    What I want the output is
    PID, Amount1, Amount2
    1, 30, 70
    2, 20, 50
    3, 30,

    Thank.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are joining and then grouping

    you should be grouping and then joining --
    Code:
    SELECT one.pid
         , one.Amount1OfSum
         , two.Amount2OfSum
      FROM ( SELECT pid
                  , SUM(Amount1) AS Amount1OfSum
               FROM TableA 
             GROUP
                 BY pid ) AS one
    LEFT OUTER
      JOIN ( SELECT pid
                  , SUM(Amount2) AS Amount2OfSum
               FROM TableB 
             GROUP
                 BY pid ) AS two
        ON two.pid = one.pid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    19
    hi r937,

    Thank for your's reply.

    I will try it tmr.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by manhjk View Post
    I will try it tmr.
    What's a tmr?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    What's a tmr?
    the day after tdy

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2011
    Posts
    19
    Hi r937,

    I got it, thank.

  7. #7
    Join Date
    Jul 2011
    Posts
    1

    join the table

    the first start make a database and then two file combine with each other.
    and then join the table

  8. #8
    Join Date
    Aug 2011
    Posts
    11
    Sorry I am new to SQL
    I executed the following query :


    SELECT one.pid
    , one.Amount1OfSum
    , two.Amount2OfSum
    FROM ( SELECT pid
    , SUM(Amount1) AS Amount1OfSum
    FROM TableA
    GROUP
    BY pid ) AS one
    LEFT OUTER
    JOIN ( SELECT pid
    , SUM(Amount2) AS Amount2OfSum
    FROM TableB
    GROUP
    BY pid ) AS two
    ON two.pid = one.pid


    and got the error ORA: 00933 sql command not properly ended....Cud you plz tell me where I went wrong ?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jason, dude, this isn't your thread, perhaps you should re-post in the oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2011
    Posts
    11

    Reply

    Okay. I will repost the same query in the oracle forum

Posting Permissions

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