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

How do temporal tables work?

912

What is meant by user defined function?

988

What is the largest value that can be stored in a byte data field?

960

What are the rules to be applied to nulls whilst doing comparisons?

1276

How do I trace sql profiler?

923

what are the type of locks ? : Sql dba

904

How do you create an update query?

917

what does the t-sql command ident_incr does? : Transact sql

1014

what is the difference between truncate and delete statement? : Transact sql

1017

What is a temporal data type?

981

How do I get sql certification?

926

Which is better trigger or stored procedure?

884

Where is pl sql used?

908

Which are sql * plus commands?

1044

what is “go” in t-sql? : Transact sql

1045