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

Group by age_c -- age_range isn't a physical column. More specifically, do this:

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

Since age_range is an aliased column, the group by is not aware of it at all. Grouping happens before the column set is calculated. The only clause that you can use your aliases in is order by , since that's the only clause that's executed after the column set is calculated.

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

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

Is that your actual code you're using? It doesn't look like it because you're missing a space between 34 and then. That code would error in SQL. Mind sharing the actual unmodified query?

Anyways, you can use a temp table or a nested query.

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

Don't forget to drop the temporary table when you're done with it

CASE WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 20 AND 29 THEN '20-29'

        WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 30 AND 39 THEN '30-39'

        WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 40 AND 49 THEN '40-49'

           WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 50 AND 59 THEN '50-59'

        ELSE '60 AND Older'

    END AS age_group_Frequency,

count (CASE WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 20 AND 29 THEN '20-29'

        WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 30 AND 39 THEN '30-39'

        WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 40 AND 49 THEN '40-49'

           WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 50 AND 59 THEN '50-59'

        ELSE '60 AND Older'

    END) AS age_group
       
     

  FROM

put your statement****

where

*****

  group by 
CASE WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 20 AND 29 THEN '20-29'

        WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 30 AND 39 THEN '30-39'

        WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 40 AND 49 THEN '40-49'

           WHEN DATEDIFF(YEAR, DateOfBirth, GETDATE()) BETWEEN 50 AND 59 THEN '50-59'

        ELSE '60 AND Older'

    END

We can't perform Group By operation directly by alias column. So I tried here by using CTE. It worked well.

with k
 as
 (
     select  
            case 
             when age >=18 and age < 30 then '18-29'
             when age>=30 and age < 50 then '30-49'
             when age>=50 and age < 60 then '50-60'
            else '60+'
        end as AgeGroup,
        purchase_amount_usd
     from shopping_trends
 )
 select AgeGroup, sum(purchase_amount_usd) as [Purchases by age group], 
 cast(((sum(purchase_amount_usd)*1.0/(select(sum(purchase_amount_usd)) from k))*100)as decimal(10,2))
 as [Purchase% by Age Group]
 from k
 group by AgeGroup
 order by [Purchases by age group] desc

Image shows the code and its output