Results 1 to 6 of 6

Thread: Need help with a complex sql select

  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Need help with a complex sql select

    Hey, I am trying to do a pretty complex sql select with a few unions. The select works in oracle just fine but when i try and use it on SQLServer2005 it blows up. It is just giving me a syntax error near 'group' but if you need information on what i need to do with it I'll post that later.

    The sql inside the parentheses work fine by itself but when you add in the surrounding text it blows up.

    Any help would be appreciated and thank you in advance.


    select Prod, Loc, sum(errors) Errors, sum(warnings) Warnings, sum(infos) Infos
    from
    (
    select f_product Prod, f_location Loc, count(*) errors, 0 warnings, 0 infos
    from f_messages
    where f_project = 90
    and f_mesg_severity = 'E'
    group by f_product, f_location
    union all
    select f_product, f_location, 0, count(*), 0
    from f_messages
    where f_project = 90
    and f_mesg_severity = 'W'
    group by f_product, fcst_location
    union all
    select f_product, f_location, 0, 0, count(*)
    from f_messages
    where f_project = 90
    and f_mesg_severity = 'I'
    group by f_product, f_location
    )
    group by Prod, Loc

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Got any error message? On which rdbms?

  3. #3
    Join Date
    Jul 2008
    Posts
    3
    The rbdm is sql server 2005 and the error its giving me is

    "Incorrect syntax near the keyword 'group'."

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    I believe sql server doesn't like 'group by' in union.

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    You need to give your sub select statement an alias, like this:

    select Prod, Loc, sum(errors) Errors, sum(warnings) Warnings, sum(infos) Infos
    from
    (
    select f_product Prod, f_location Loc, count(*) errors, 0 warnings, 0 infos
    from f_messages
    where f_project = 90
    and f_mesg_severity = 'E'
    group by f_product, f_location
    union all
    select f_product, f_location, 0, count(*), 0
    from f_messages
    where f_project = 90
    and f_mesg_severity = 'W'
    group by f_product, fcst_location
    union all
    select f_product, f_location, 0, 0, count(*)
    from f_messages
    where f_project = 90
    and f_mesg_severity = 'I'
    group by f_product, f_location
    ) AS X
    group by Prod, Loc

  6. #6
    Join Date
    Jul 2008
    Posts
    3
    thank you so much for the help, fixed it perfectly

Posting Permissions

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