|
Subject:
|
Do i have to use a subquery, if yes then how?
|
|
Posted By:
|
code_lover
|
Post Date:
|
1/1/2007 11:58:51 AM
|
I have an emp table in scott schema and i have to find out the names of employees whose annual salary is lesser than the average salary in their department.
The structure of the table is as follows:
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
|
|
Reply By:
|
sshelper
|
Reply Date:
|
1/2/2007 11:25:36 AM
|
Try the following query:
SELECT * FROM scott.emp emp INNER JOIN
(SELECT DEPTNO, AVG(SAL) AS AvgSal FROM scott.emp GROUP BY DEPTNO) sal
ON emp.DEPTNO = sal.DEPTNO
WHERE emp.SAL < sal.AvgSal
SQL Server Helper
http://www.sql-server-helper.com
Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp
|
|
Reply By:
|
code_lover
|
Reply Date:
|
1/2/2007 1:22:30 PM
|
It is the average annual salary and the average salary for which we have to do the comparision.Your query has given me a lot of help by the way.I am further looking in to it.
Can you just modify it to retrieve employees who have annual salaries lesser than the average annual salaries of their departmnents.
The comparision has to be between the following 2 queries:
select sal*12
from emp;
select avg(sal*12)
from emp
group by deptno;
Please help!
|
