SQL Issue with my query

Question

I have an issue with the query, that operates on following talble:

+---------------------------+
| ID NAME SALARY DEPARTMENT |
+---------------------------+
| 1 John 100 Accounting     |
| 2 Mary 200 IT             |
+---------------------------+

What I am trying to achive, is find the query, that will result in the following:

For each employe, find the average salary of those employes whose salary is either up to 100 more or 100 less then salary of given employee, and they work in the same department.

So far I have this:

SELECT E1.ID, AVG(E2.SALARY) FROM E1 EMP, E2 EMP
WHERE ABS(E1.SALARY-E2.SALARY)<= 100 AND E1.DEPARTMENT = E2.DEPARTMENT
GROUP BY E1.NAME

Is this correct?

Giorgos Betsos · Accepted Answer · 2016-04-23 14:45:34Z

2

You'd better use explicit join syntax:

SELECT E1.ID, AVG(E2.SALARY) 
FROM EMP E1
JOIN EMP E2 
   ON E1.ID <> E2.ID AND 
      E1.DEPARTMENT = E2.DEPARTMENT AND
      ABS(E1.SALARY - E2.SALARY) <= 100 
GROUP BY E1.ID

Predicate E1.ID <> E2.ID is necessary in case you don't want to include the salary of the same employee in the average calculation.

answered Apr 23, 2016 at 14:45

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

uksz Over a year ago

Is there any performance difference between those two operations?

uksz Over a year ago

Btw, thanks for pointing out my mistake about including the salary of the same employee!

Giorgos Betsos Over a year ago

@uksz Yes, because your query first fetches a cartesian product of the employee table to itself and then applies the filtering conditions.

Collectives™ on Stack Overflow

SQL Issue with my query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related