>>>>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.
>
>Are you missing an alias for the derived table?
>
>It should work OK - what is your error?

Oh my . . . I just discovered another problem that I have to deal with first. With my test data, the first query returns 1 record, the second returns 179, but when I combine them with the UNION I only get 19.Coincidentally, 19 is the number that should return when the rtastatus field is aggregated. I thought perhaps the WHERE clauses were causing this but that is not the case. I also changed the aliases in the second SELECT so they were not reused but that also made no difference. Any idea what gives? I was expecting 180 records.