Re: 14 Hours on a simple Problem Darn!!!

What you really need is the 'analytic functions' built into SQL in Oracle 8.1.6.

For earlier versions of Oracle there are a few variations on what you are trying to do, but the bottom line is that you need to run three queries and join the results.

1. select sum(salary) company_sal from employees;
2. select dept_no, sum(salary) dept_sal from employees group by dept_no
3. select id, dept_no, salary from employees;

Convert to in-line views, and join:

select id, salary, salary/dept_sal, salary/company_sal from

    (select sum(salary) company_sal from employees) v1,     (select dept_no, sum(salary) dept_sal from employees group by dept_no) v2,

    (select id, dept_no, salary from employees) v3 where

        v2.dept_no = v3.dept_no
;

Early versions of PL/SQL will reject this, but it should work in all currently supported versions of SQL

There are several ways to re-write this, I have chosen this approach purely for the uniformity of presentation

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

GMon wrote in message <8b2v8a$8rf$1_at_slb2.atl.mindspring.net>...
>I'm attempting to create 2 columns of data, % of department salary and % of
>company salary for each employee in the company. I've designed queries
>using the GROUP BY Clause to come up with % by department but not employee.
>What am I missing here?
>
>
Received on Sun Mar 19 2000 - 13:01:40 CST