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

Got any error message? On which rdbms?

The rbdm is sql server 2005 and the error its giving me is

"Incorrect syntax near the keyword 'group'."

I believe sql server doesn't like 'group by' in union.

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

thank you so much for the help, fixed it perfectly