-
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....?
-
Re: To find ratio !!!
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]
-
Re: To find ratio !!!
Fantastic krishna..
I got the result.... I did some changes in the above code according to my table structure & got it...
Great job :) :)
-
Re: To find ratio !!!
>>[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 ..
-
Re: To find ratio !!!
You have not provided table structures. Just I have given model query.
Any how you have done modifications as per your requirement.
Regards
Krishna