To find ratio !!!

Hi, i have the following tables... Employee, manager, dept & payscale.

I need to find the ratio of manager and employees salary. I found out managers' and employees' salary individually.....
Select sum(p.basicsal) manager_sal from employee e, manager m, payscale p where e.empid = m.empid and p.empid = e.empid;

select sum(p.basicsal) employee_sal from employee e, manager m, payscale p where e.empid != m.empid and p.empid = e.empid;

i need to join above two queries and have to find out the ratio... Can any one help me out....?

Try this query

[CODE]
SELECT a.empid,
nvl(b.mgrid,0),
a.empsal,
b.mgrsal,
nvl(b.mgrsal,0)/a.empsal as ratio
FROM
(
SELECT e.empid,
sum(p.basicsal) empsal
FROM employee e,Payscale p
WHERE e.empid = p.empid
GROUP BY e.empid
)a LEFT OUTER JOIN
(
SELECT m.mgrid,
m.empid,
sum(p.basicsal) mgrsal
FROM manager m, payscale p
WHERE m.mgrid = p.empid
GROUP BY m.mgrid,m.empid
)b
ON a.empid = b.empid
[/CODE]

Fantastic krishna..

I got the result.... I did some changes in the above code according to my table structure & got it...

Great job :) :)

>>[B]I did not get it right... I got the output as zero... [/B]

If an employee does not has manager then it will display zero.

I have tested it. It is working fine for me.

Let me know what exactly your required output is ..

You have not provided table structures. Just I have given model query.

Any how you have done modifications as per your requirement.


Regards
Krishna