Re: A difficult SQL problem... please help

From: Art S. Kagel <kagel_at_bloomberg.net>
Date: 1998/06/24
Message-ID: <35912B94.62B_at_bloomberg.net>#1/1


Lee Ka Wo wrote:
>
> Dear Experts,
>
> I get a problem....
>
> If I have a tables full of salary., I need to group them into several
> salary "ranges" like this:
>
> <1000 >=1000 to < 2000 >=2000 to <=2500 >2500
>
> Male 100 200 120 50
> Famale 50 222 30 65
>
> What should I do ??

create table matrix (
sex char(1),

rng1 int,
rng2 int,
rng3 int,

rng4 int );

insert into matrix(sex) values ("M");
insert into matric(sex) values ("F");

update matrix
set rng1 = (

    select count(*) from salarytbl
    where salary < 1000 and sex = "M" )
where sex = "M";

update matrix
set rng2 = (

    select count(*) from salarytbl
    where salary between 1000 and 2000 and sex = "M" ) where sex = "M";

update matrix
set rng3 = (

    select count(*) from salarytbl
    where salary between 2000 and 2500 and sex = "M" ) where sex = "M";

update matrix
set rng4 = (

    select count(*) from salarytbl
    where salary > 2500 and sex = "M" )
where sex = "M";

....etc...

Then:

select sex, rng1 "<1000", rng2 ">=1000 to < 2000",

       rng3 ">=2000 to <=2500", rng4 ">2500" from matrix
order by sex desc;

Art S. Kagel Received on Wed Jun 24 1998 - 00:00:00 CEST

Original text of this message