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

Can we use join in subquery?

1103

What are sql procedures?

1031

How many postgresql users are there, worldwide?

1068

Can we use views in stored procedure?

1036

What is sqlcommand?

1032

How do you explain an index?

1287

Why do we use cursors?

994

Is sql easier than java?

1020

What is dml and ddl?

966

Can we use pl sql in sql server?

1088

Explain select statements in sql?

1061

What are the advantages of indexing?

986

Explain how exception handling is done in advance pl/sql?

1001

What is type and rowtype in pl sql?

1021

How can you load multi line records? : aql loader

1082