15,900,461 members
3.00/5 (1 vote)
See more:
Hi
I have table like this:

Student Age
A 22
B 28
C 21
D 22
E 4
F 9

I want result like this:

Count Decscription
2 Students below 10 years
3 Students between 10 - 20
4 Student above 20

I can do it already by grouping and doing unioun of three sub quesries.

I sthere a better way?
Posted
frostcox 22-Aug-13 12:39pm
You could join the table on to itself and get your result from that
RedDk 22-Aug-13 13:13pm
What do the grouping and union of three queries look like?
create another table with AgeRange(ID,Start,End,Description)
Add data like (1,0,10,'Students below 10 years')
Add data like (2,10,20,'Students between 10 - 20')
Add data like (3,20,100,'Student above 20') then use between clause & group by

## Solution 1

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

Maciej Los 22-Aug-13 14:14pm
Good work! I like the second solution ;)
+5!
scottgp 22-Aug-13 14:24pm
thanks!