Re: Percentage caclulation - dealing with different frequences

In article <9u2hj8$4c6g$1_at_as201.hinet.hr>, "Dario says...
>
>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?

  sum( decode( sign(annual_income-125000), 1, 1, 0 ) )/count(name)

the sign will return +1 when annual_income > 125000, 0 when it equals and -1 when its less then. We'll use decode to get either a 1 or 0 back. We sum these up and divide by the number of observations.

>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
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp