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.

Stricker

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

Illuminator

Thanks Joe... I appreciate the simplicity. – Stricker

Simple and best! – Caren

Answer 2

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.

Tymon

@Sheridanbulger this is exactly what was asked for and works fine. See an working example at Grouping By Case Example – Apathetic

Oh I'm sorry. At first glance I saw grouping by just age_c. Did you edit it? In either case, yes that code (or any of the other answers) would work great. – Mia

Thank you. I really didn't think I could use age_range as a colunn because it is an alias. I was following the example on this page: [link]#3248130 – Stricker

Answer 3

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

Match

I'd prefer a temp table to a nested query just because it makes a cleaner looking query, but it's really two sides to the same coin. – Mia

Thanks Mithrandir. This is very close, I had to rearrange it a little to get it to work... but work it does. – Stricker

Answer 4

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

Sapper

Answer 5

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

Mia

It doesn't look like it because you're missing a space between 34 and then. That code would error in SQL. not really. SQL Server can parse and execute it with no problem. – Everson

Answer 6

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

Probate

Answer 7

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

Pollack

I used CTE method to do Group By by AgeGroup Column in my code. It worked well. I uploaded image of code and its output. – Pollack

Please edit your answer to include that explanation. – Wareroom

Answer 8

WITH AgeGroupList AS
    (
        SELECT  GroupID =   1, GroupName = 'Under 18', GroupMin =  0, GroupMax = 17 UNION
        SELECT  GroupID =   2, GroupName = '18 - 24 ', GroupMin = 18, GroupMax = 24 UNION
        SELECT  GroupID =   3, GroupName = '25 - 34 ', GroupMin = 25, GroupMax = 34
    )
, GroupCountList AS
    (
        SELECT  DISTINCT
                GroupID
            ,   GroupName
            ,   GroupCount  =   COUNT(*) OVER(PARTITION BY GroupID)
        FROM    Contacts        C
        JOIN    AgeGroupList    G   ON  C.Age_C BETWEEN G.GroupMin AND GroupMax
    )

SELECT
        GroupName
    ,   GroupCount
FROM    GroupCountList
ORDER BY
        GroupID

Selfstarter

With the code above, you can add/update/delete as many age groups as you want and it will still work as expected. – Selfstarter