Re: Percentage caclulation - dealing with different frequences

Thank you.

Dario

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9u2rsh026st_at_drn.newsguy.com...
> 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
>
Received on Wed Nov 28 2001 - 11:14:52 CST