| Getting highest sal in the dept [message #7391] |
Wed, 11 June 2003 03:57  |
Sowmya
Messages: 5
Registered: August 2002
|
Junior Member |
|
|
|
can any one give me the query to get the name,sal,dept of employee whoose salary is greater than the avg salary in each dept. (For each dept the employee name shd come with the given condition)
|
|
|
|
| Re: Getting highest sal in the dept [message #7393 is a reply to message #7391] |
Wed, 11 June 2003 04:17  |
Sachin Rananavare
Messages: 2
Registered: June 2003
|
Junior Member |
|
|
you can get the avg sal by the query
select deptno, avg(sal) from emp
group by deptno ;
The output will be
DEPTNO AVG(SAL)
--------- ---------
10 2512.5
20 2175
30 1566.6667
The query you require which gives salary greater than the avg salary in each dept.
select emp.deptno, ename, sal, avgsal from emp, (select deptno, avg(sal) avgsal from emp
group by deptno ) b
where emp.deptno = b.deptno
and emp.sal > b.avgsal;
the output of this will be
DEPTNO ENAME SAL AVGSAL
--------- ---------- --------- ---------
10 KING 5000 2512.5
20 FORD 3000 2175
20 SCOTT 3000 2175
20 JONES 2975 2175
30 ALLEN 1600 1566.6667
30 BLAKE 2850 1566.6667
6 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
