Re: SQL + questions

Hi Lee,

> I have a SQL plus question. I have a table like this (just five
> records):
>
> Name Age
> ------------ ---
> chan tai man 10
> lee ka wo 20
> young man 21
> old man 60
> old woman 62
>
> I want to do a query to count the number of
> people with age <60 (young) and number of people >=60 (old). I hope t=
he
> result will
> be like this:
>
> Count(*) Age_range
> -------- ---------
> 3 young
> 2 old
>
> I wonder whether the following statement can serve my purpose.
>
> select count(*), decode(age, <60,'young',>=60,'old')Age_range from
> people

1. ) simple solution #1 when only two values

select count( age ) , 'YOUNG' age_range from where people where age < 60
union all
select count( age ) , 'OLD' age_range from where people where=

age > 59

2.) Simple solution #2

  select decode( sign(age - 60), -1, 'young','old') age_categorie , count( age )
  from people
  group by decode( sign(age - 60), -1, 'young','old')

3.) relational solution
As I guess that you don't have always only two values think of definin= g
a table where you store age ranges
create table age_ranges ( start_level number , end_level number , age_level_name varchar2(30 ) ) ; Fill it
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 0, 2 , 'baby') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 3, 4 , 'toddler') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 5, 6 , 'kindergarten') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 7, 10 , 'primaryschool') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values (11, 14 , 'secondaryschool') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values (15, 18 , 'college') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 19, 24, 'highschool') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 25, 60, 'working') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 61, 90, 'senior') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values ( 91, 99, 'methusalem') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values (100, 110, 'greenteatrinker') ;
insert into age_ranges ( start_level , end_level , age_level_name ) values (110, 5900, 'ötzi') ;

and then

select age_level_name , count( age ) from age_ranges r, people p where p.age between start_level and end_level group by age_level_name ;

Received on Sat Jun 20 1998 - 08:46:04 CDT