SQL Group by Age Range

Question

SQL 2005, I have a table with a column 'ages_c', I need to group the records by age ranges. This is the query that I found on this site and it's getting me 90% there but the 'group by' is erroring, *Invalid column name 'age_range'*

 select 
  case
   when age_c <18 then 'Under 18'
   when age_c between 18 and 24 then '18-24'
   when age_c between 25 and 34then '25-34'
 END as age_range, 
 Count(*) as count
 from contacts
 group by age_range
 order by age_range

When I group and order by 'age_c' my result is:

  Under 18  1
  18-24 1
  18-24 1
  25-34 1

What I want is:

 Under 18   1
  18-24 2      
  25-34 1

Thanks.

Answer 1

Try it this way instead:

 SELECT SUM(CASE WHEN age_c < 18 THEN 1 ELSE 0 END) AS [Under 18],
        SUM(CASE WHEN age_c BETWEEN 18 AND 24 THEN 1 ELSE 0 END) AS [18-24],
        SUM(CASE WHEN age_c BETWEEN 25 AND 34 THEN 1 ELSE 0 END) AS [25-34]
 FROM contacts

Answer 2

You can't group by a column you create in the query. You'll have to do it like this:

SELECT count(*), * FROM 
(
select 
  case
   when age_c <18 then 'Under 18'
   when age_c between 18 and 24 then '18-24'
   when age_c between 25 and 34then '25-34'
 END as age_range 
 from contacts
) t
group by age_range
order by age_range

or GROUP BY

case
       when age_c <18 then 'Under 18'
       when age_c between 18 and 24 then '18-24'
       when age_c between 25 and 34then '25-34'
END