Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    Amsterdam
    Posts
    18

    Unanswered: Subquery Problem

    I need to sum table1 and then join it on table2 (which has also been summed. (Grouped by name and data)
    My select statement is returning absolute garbage.

    At the end of the end of the day, the query should return three rows:-

    Carl honda 425
    Carl suzuki 15
    Ryan suzuki 298


    Table1
    Name Data Amount
    Carl honda -12
    Carl honda 400
    Ryan suzuki 298

    Table2
    Name Data Amount
    Carl honda 10
    Carl suzuki 15
    Carl honda 15
    Carl honda 12

    Many thanks

  2. #2
    Join Date
    Mar 2003
    Location
    Amsterdam
    Posts
    18
    Thanks for your assistance!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select x.Name, x.Data, sum(x.Amount)
    from ( select Name, Data, Amount from table1
    union
    select Name, Data, Amount from table2 ) as x
    group by x.Name, x.Data


    rudy

  4. #4
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    It needs an ALL in the Union

    select x.Name, x.Data, sum(x.Amount)
    from ( select Name, Data, Amount from table1
    union ALL
    select Name, Data, Amount from table2 ) as x
    group by x.Name, x.Data

    Tim S

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, tims

    quite right

    did enron teach me nothing? can't be dropping no "duplicate" rows in a financial application...

Posting Permissions

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