Oleg

• Sep 25th, 2007

 

In general it will be:

SELECT a.name, a.department
FROM my_table a,
    (select name, department, avg(salary) as avg_salary
    from my_table
    group by name, department) b
WHERE a.name = b.name
and a.department = b.department
and a.salary > v.avg_salary

kowmudiswarna

 Profile Answers by kowmudiswarna Questions by kowmudiswarna

• Sep 25th, 2007

 

Hi Oleg,

Thanks  to reply my query,

And here is the simple way to display the employee records who is getting sal more than the avg sal in their department is..............

select * from emp a where sal>(select avg(sal) from emp where e.deptno=deptno);


In the above command  emp    is the table name ,  sal is the column name of salary and deptno is the colmun name of  department number.

joybanerjee

 Profile Answers by joybanerjee Questions by joybanerjee

• Dec 15th, 2007

 

SELECT EMPH_CD,EMPH_EMP_NAME, SUM(EMPD_AMT) FROM  EMP,EMP_det

WHERE EMPH_CD=EMPD_EMP_CD AND EMPH_RESG_DT IS NULL AND EMPD_SALH_TYPE='FERN' AND EMPH_DEPT_CD=20

HAVING SUM(EMPD_AMT)>(SELECT SUM(SUMM)/COUNT(CD)

FROM(

select SUM(EMPD_AMT)SUMM,EMPH_CD CD

from emp,emp_det where emph_resg_dt is null

and emph_cd=empd_emp_cd

and EMPH_DEPT_CD=20

and EMPD_SALH_TYPE='FERN'

GROUP BY EMPH_CD

))GROUP BY EMPH_CD,EMPH_EMP_NAME

askvenki

 Profile Answers by askvenki 

• Jun 3rd, 2008

 

SELECT * FROM EMP E WHERE E.SAL>(SELECT AVG(sAL) FROM EMP E1 WHERE E1.DEPTNO=E.DEPTNO);

Above is the co-related subquery, I am explaining its execution

Step1: The main query executes first and we get as deptno from the first row (ex. 10);

Step2: The subquery executes with condition part of alias name as outer query example 'E' passing 10;

Step3:
Again outer query decides to print based on subquery values.

sravee123

 Profile Answers by sravee123 

• Jun 4th, 2008

 

select e.ename,sal from emp e where e.sal>(select avg(sal) from emp where e.deptno=deptno)