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)

__________________
MAXOOD!

Life is an endless journey towards perfection

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

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!