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

>>I did not get it right... I got the output as zero...

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