Another way
with cte as (
select [State], County, Sex, class,
case when Agegroup in ('15-17 yrs', '18-20 yrs', '21-24 yrs') then '15-24 yrs' else Agegroup end AS [Agegroup],
[total]
from #temp)
select [State], County, Sex, class, Agegroup, sum(total) AS [total]
from cte
group by [State], County, Sex, class, Agegroup
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537