Percentage caclulation - dealing with different frequences

Hi there,

Suppose I have a table:
create table TEST( name varchar2(50), year number(4), gender varchar2(1), birth_date date, annual_income number(8,2) );

I fill it with data:

insert into test values('JOHN',2000,'M','31.12.1950',120000);
insert into test values('BRUCE',2000,'M','30.10.1969',95000);
insert into test values('MARY',2000,'F','28.10.1977',70000);
insert into test values('FRANK',2000,'M','28.10.1965',175000);
insert into test values('JOHN',2001,'M','31.12.1950',130000);
insert into test values('BRUCE',2001,'M','30.10.1969',94000);
insert into test values('MARY',2001,'F','28.10.1977',75000);
insert into test values('FRANK',2001,'M','28.10.1965',183000);

I am trying to get the following output: select year, gender, sum(annual_income) as total_income, COUNT(name) number_of_employees, NULL as pct_having_income_over_125000 from test
group by year, gender
 YEAR GENDER TOTAL_INCOME NUMBER_OF_EMPLOYEES PCT_HAVING_INCOME_OVER_125000
----- ------ ------------ ------------------- -----------------------------

 2000 F             70000                   1
 2000 M            390000                   3
 2001 F             75000                   1
 2001 M            407000                   3

How can I calculate the values in column PCT_HAVING_INCOME_OVER_125000, that is
percentage of people having income over 125000 for a given row? I need the following result:
 YEAR GENDER TOTAL_INCOME NUMBER_OF_EMPLOYEES PCT_HAVING_INCOME_OVER_125000
----- ------ ------------ ------------------- -----------------------------

 2000 F             70000                   1 0%
 2000 M            390000                   3 33.33%
 2001 F             75000                   1 0%
 2001 M            407000                   3 66.66%

Thank you.
Dario Received on Wed Nov 28 2001 - 05:32:10 CST