Click here to Skip to main content
15,900,461 members
Please Sign up or sign in to vote.
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
Comments
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?
pradiprenushe 22-Aug-13 13:59pm    
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

1 solution

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
 
Share this answer
 
Comments
Maciej Los 22-Aug-13 14:14pm    
Good work! I like the second solution ;)
+5!
scottgp 22-Aug-13 14:24pm    
thanks!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900