Group By alias in SQL

Question

I have seen other group by alias examples. However I can't seem to make it work for my example.

I have a table with people info which I want to group by age categories. (20 - 25, 26 - 30, 31- 35, 36 -40 and >40)

enter image description here

Code I managed so far is:

select sub  
from   (    
  select  Case 
            when Age <26 then'20-26' 
            when Age <31 then '26-30'    
            when Age < 36 then '31-35'    
            when Age <41 then '36-40'    
            else '>40' 
          end    
  from people
) as sub
Group by sub

I want to get the total number of people that belongs to each age group.

You can't group by the name of a table alias, only a column alias. Also `select `is illegal in SQL Server — , Jul 03 '20 at 06:15
Does this answer your question? [In SQL, how can you "group by" in ranges?](https://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges) — Preben Huybrechts, Jul 03 '20 at 06:17

score 3 · Answer 1 · answered Jul 03 '20 at 06:17

Use a CTE to generate the ranges, then aggregate over that CTE:

WITH cte AS (
    SELECT CASE Age WHEN < 26 THEN '20-26'
                    WHEN < 32 THEN '26-30'
                    WHEN < 36 THEN '31-35'
                    WHEN < 41 THEN '36-40'
                    ELSE '> 40' END AS age_group
    FROM people
)

SELECT age_group, COUNT(*)
FROM cte
GROUP BY age_group;

Note that on some other database vendors, such as MySQL and SQLite, you can actually GROUP BY an alias defined in a select of the same query, so you would not even need a formal CTE/subquery in that case.

score 1 · Answer 2 · answered Jul 03 '20 at 07:10

Please use below query,

select age_category, count(1) from
(select 
case when Age between 0 and 25 then '20-25'
     when Age between 26 and 30 then '26-30'
     when Age between 31 and 35 then '31-35'     
     when Age between 36 and 40 then '36-40'
     else '>40' end as age_category
from people) qry group by age_category;

Andrei Odegov · Answer 3 · 2020-07-03T08:26:20.340

You can use the case expression directly in the group by clause.

select
  case
    when Age < 26 then '20-26'
    when Age < 31 then '26-30'
    when Age < 36 then '31-35'
    when Age < 41 then '36-40'
    else '>40'
  end as age_cat,
  count(*)
from people
group by
  case
    when Age < 26 then '20-26'
    when Age < 31 then '26-30'
    when Age < 36 then '31-35'
    when Age < 41 then '36-40'
    else '>40'
  end;

Alternatively, you can calculate the values for the group by clause in the cross apply operator.

select
  ca.age_cat,
  count(*)
from people
cross apply(
  select
    case
      when Age < 26 then '20-26'
      when Age < 31 then '26-30'
      when Age < 36 then '31-35'
      when Age < 41 then '36-40'
      else '>40'
    end as age_cat
) as ca
group by ca.age_cat;

Demo.

Gordon Linoff · Answer 4 · 2020-07-03T13:24:38.467

1

If you want your result set to include groups that have zero people, I would recommend a derived table approach:

with age_groups as (
      select *
      from (values (0, 26, '20-26'),
                   (26, 31, '26-30'),  
                   (31, 36, '31-35'),  
                   (36, 41, '36-40')   
                   (41, null, '>40')
           ) v(min_age, max_age, grp) 
      )
select ag.grp, count(p.age)  
from age_groups ag left join
     people p
     on p.age >= ag.min_age and (p.age < ag.max_age or ag.max_age is null)
group by ag.grp
order by min(ag.min_age);

edited Jul 03 '20 at 13:24

answered Jul 03 '20 at 12:26

Gordon Linoff

1,242,037
58
646
786

For last age group `p.age < null` will null propagate to false. – wqw Jul 03 '20 at 13:10

score 0 · Answer 5 · answered Jul 04 '20 at 02:05

Thanks all for your help. I used the below and it worked !!!

select t.agecat as[age_range],count(*) as [Number of People]
from (
select case
when age <26 then '20-25'
when age <31 then '26-30'
when age <36 then '31-35'
when age<41 then '36-40'
else '>40' end as agecat
from people) t
group by t.agecat

Group By alias in SQL

5 Answers5