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
19960 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

Is pl sql better than sql?

1042

What is database migration?

973

How many types of literals are available in pl sql?

1122

What are all the different types of indexes?

1152

Explain what is dbms?

1071

In what condition is it good to disable a trigger?

1015

Are subqueries faster than joins?

1024

How we can update the view?

1054

Define commit?

1010

Why do we create views in sql?

1156

define sql update statement ? : Sql dba

1126

What is sql in oracle?

1068

How do you declare a constant?

965

How many rows can sqlite handle?

1121

What is the clause we need to add in function body to return variable?

1015