select * from (select id,(case when sal < avg(sal) over(partition by id)

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 / mahalakshmi s

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

Answer Posted / mahalakshmi s

select * from (select id,(case when sal < avg(sal) over(partition by id) then sal else 0 end) avg_val from dept) a where avg_val !=0;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Is oracle sql free?

1037

Does sql full backup truncate logs?

1080

Explain what is sql?

1180

What is the starting oracle error number? What is meant by forward declaration in functions?

1041

What are sql constraints?

1228

How to select unique records from a table?

1110

Why do we create stored procedures & functions in pl/sql and how are they different?

995

What is trigger in pl sql with examples?

1082

What is sorting in sql?

985

What is sql table?

1022

What is difference between table and view?

1136

What is sql and also describe types of sql statements?

1114

What is a record in a database?

1102

What is a schema sql?

1226

what is collation? : Sql dba

1205