>>Hi -
>>
>>I need to combine 2 similar queries with a UNION . Easy enough, except that I also need an aggregate function. If I place the aggregate function in each of the 2 queries, the result contains a separate aggregate for each of the 2 queries. (No surprise there) What I can't figure out is how to produce the aggregate AFTER the 2 queries have been combined. One thought I had was to use 2 queries with the union but no aggregate and then a 3rd query that would add the aggregate function, but I couldn't figure out how to put the first query into a #Temp table to set up the 3rd query. Here is what I have at present but it has the problem mentioned earlier.
>>

>>SELECT c.Element as RTAElement, c.subelement, a.developmentstatus as rtastatus, COUNT("developmentstatus") as total, c.ElementNum
>>FROM TIP_ProjectS a
>>inner join RTA_Elements c ON convert(integer,SUBSTRING(a.RTAID,5,2)) = c.ElementNum
>>WHERE RtaID <> '' and (a.DevelopmentStatus='In Design' or a.DevelopmentStatus='Complete' or a.DevelopmentStatus='In Construction')
>>GROUP BY c.Element, c.subelement, a.developmentStatus, c.ElementNum
>>union
>>select  b.RTAElement, b.subelement, a.rtastatus, COUNT("rtastatus") as total ,c.ElementNum
>>from RTA_SubProjects a
>>inner join [RTA_GeneralInfo] b on a.tipid = b.tipID 
>>inner join [RTA_Elements] c ON b.RTAElement = c.Element AND b.subelement = c.subelement
>>WHERE (a.RTAStatus='In Design' or a.RTAStatus='Complete' or a.RTAStatus='In Construction')
>>group by b.RTAElement, b.subelement, a.RTAStatus, c.ElementNum
>>ORDER BY c.ElementNum

>>
>>Any thoughts?
>>Thanks
>
>Use derived table, e.g.
>
>select sum(Something) from (complex query ) X order by ... -- order by should not be in the derived table.

Thanks Naomi -

So the complex query would look like this

SELECT c.Element as RTAElement, c.subelement, a.developmentstatus as rtastatus, c.ElementNum
FROM TIP_ProjectS a
inner join RTA_Elements c ON convert(integer,SUBSTRING(a.RTAID,5,2)) = c.ElementNum
WHERE RtaID <> '' and (a.DevelopmentStatus='In Design' or a.DevelopmentStatus='Complete' or a.DevelopmentStatus='In Construction')
union
select  b.RTAElement, b.subelement, a.rtastatus, c.ElementNum
from RTA_SubProjects a
inner join [RTA_GeneralInfo] b on a.tipid = b.tipID 
inner join [RTA_Elements] c ON b.RTAElement = c.Element AND b.subelement = c.subelement
WHERE (a.RTAStatus='In Design' or a.RTAStatus='Complete' or a.RTAStatus='In Construction')

(which works)

but when I frame it with SELECT * FROM (complex query), it does not. I am missing something in the syntax.