select * from (select eno,dno,sal,avg(sal) over(partition by dno) as avg

i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100

i want to get sal which is less than the avg sal of thri dept.

eno dno sal
2 10 150
3 10 100
4 20 75

Question Posted / biswa

12 Answers
19538 Views
IBM, I also Faced
E-Mail Answers

Answer Posted / biswa

select *
from (select eno,dno,sal,avg(sal) over(partition by dno) as
avg_sal
from emp)
where sal<avg_sal;

OR

select e1.empno,e1.deptno,e1.sal
from emp e1
where sal>(select avg(sal) from emp e2
where e1.deptno=e2.deptno)

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is the sql*loader? : aql loader

1010

Explain what is sql?

1064

What are the differences between implicit and explicit cursors?

1005

What is #table in sql?

1001

What is difference between table and view?

980

How would you convert date into julian date format?

949

What are the types of views in sql?

1041

how to convert numeric values to character strings? : Sql dba

1039

How can we optimize a sql query?

985

what are the t string functions available in tsql? : Transact sql

949

Is inner join faster than left join?

1125

What is a call statement? Explain with an example.

955

what are dynamic queries in t-sql? : Transact sql

964

What is set serveroutput on?

1090

What version is sql?

967