SQL group by age

Question

SQL 2005, I have a table with the column "age_c", I need to group records by age ranges. This is the query I found on this site and it gets me 90%, but "group by" is an error, * 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 "age_c", my result is:

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

I want to:

 Under 18   1
  18-24 2      
  25-34 1

Thank.

Answer 1

Try this 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

Group age_c- age_range is not a physical column. In particular, do the following:

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

age_range , group by . , . , , order by, , .

Answer 3

, ? , 34 . ​​SQL. , ?

.

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 34 THEN '25-34'
END AS age_range, 
INTO #TempAges
FROM contacts
ORDER BY age_c

SELECT COUNT(*) FROM #TempAges GROUP BY age_range

,

Answer 4

, . :

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

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

Answer 5

If your database supports FILTER WHERE syntax , then this can be archived in a very elegant way:

SELECT COUNT(id) FILTER (WHERE (age < 18)) AS "Under 18",
       COUNT(id) FILTER (WHERE (age >= 18 AND age <= 24)) AS "18-24",
       COUNT(id) FILTER (WHERE (age >= 25 AND age <= 34)) AS "25-34"
FROM contacts

Or this one, if not:

SELECT count(CASE WHEN (age < 18) THEN id ELSE null END)                AS "Under_18",
       count(CASE WHEN (age >= 18 AND age <= 24) THEN id ELSE null END) AS "18-24",
       count(CASE WHEN (age >= 25 AND age <= 34) THEN id ELSE null END) AS "25-34"
FROM contacts