Monday, April 25, 2011

how to group by range

i have table it having age column i need count between age groupsbelow query all individual age record counts

create table agelimit(age number(10));

insert into agelimit values(10);
insert into agelimit values(10);
insert into agelimit values(12);
insert into agelimit values(20);
insert into agelimit values(30);
insert into agelimit values(40);
insert into agelimit values(45);
insert into agelimit values(60);
commit

SELECT CASE WHEN age <= 10 THEN '1-10'
WHEN age <= 20 THEN '11-20'
WHEN age <=30 THEN '21-30'
WHEN age <=40 THEN '31-40'
WHEN age <=50 THEN '41-50'
ELSE '51+' END AS age, COUNT(*) AS n
FROM agelimit GROUP BY CASE WHEN age<= 10 THEN '1-10' WHEN age <= 20 THEN '11-20'
WHEN age <=30 THEN '21-30'
when age <=40 THEN '31-40'
WHEN age <=50 THEN '41-50'
ELSE '51+' END