Your test data didn't look complete (no Students between 10 - 20), so I expanded it, and here's a sample query:
create table test2
(student varchar(10),
age int)
insert into test2 values ('A',22);
insert into test2 values ('B',28);
insert into test2 values ('C',21);
insert into test2 values ('D',22);
insert into test2 values ('E',4);
insert into test2 values ('F',9);
insert into test2 values ('X',11);
insert into test2 values ('Y',12);
insert into test2 values ('Z',13);
select COUNT(*), age_range
from (
select
case
when age <10 then '1. Students below 10 years'
when age between 10 and 20 then '2. Students between 10 - 20'
when age >20 then '3. Student above 20'
end age_range
from test2) v1
group by age_range
order by 1
(No column name) age_range
2 1. Students below 10 years
3 2. Students between 10 - 20
4 3. Student above 20
However, when I had to do something similar recently, I preferred to create a "range" table to join against, in case the range requirements changed:
create table age_range
(rng_sort int,
rng_desc varchar(25),
rng_start int,
rng_end int)
insert into age_range values (1,'Students below 10 years',0,9);
insert into age_range values (2,'Students between 10 - 20',10,20);
insert into age_range values (3,'Student above 20',21,999);
select rng_sort, COUNT(*), rng_desc
from test2 inner join age_range ar
on test2.age between rng_start and rng_end
group by rng_sort,rng_desc
order by rng_sort
rng_sort (No column name) rng_desc
1 2 Students below 10 years
2 3 Students between 10 - 20
3 4 Student above 20
Scott