Post reply

select UNION

  • May 15, 2006 at 11:38 am

    #117565

    I have a UNION query that I want to select from.

    select sum(col1),col2 from

    (select col1,col2 from table1 union select col1,col2 from table2)

    This doesn't work. Can it be done this way?

  • May 15, 2006 at 11:52 am

    #637943

    because of the sum function, you have to GROUP BY in the outer select: try this syntax:

    select sum(col1),col2 from

    (select col1,col2 from table1 union select col1,col2 from table2)

    GROUP BY COL2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 15, 2006 at 12:38 pm

    #637964

    Lowell,

    Thanks for the reply. I know what your talking about but the error I get is not about that. I didn't post exactly what I was writing in the query. Actually if I just do:

     

    select col1 from (select col1,col2 from table1 union select col1,col2 from table2)

    it does not work. I get:

    Incorrect syntax near ')'.

     

  • May 15, 2006 at 12:43 pm

    #637965

    ahh;

    the subquery in the parenthesis needs an alias:

    this fails: select name from (select name,id from sysobjects union select name,id from syscolumns)

    this works:select name from (select name,id from sysobjects union select name,id from syscolumns) x

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 15, 2006 at 12:57 pm

    #637972

    Very nice....I figured it was something simple but you never know. Thanks again Lowell. Hopefully I can return the favor one day!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply