CODEWITHSUNDEEP
sql
uksz
uksz

Reputation: 18719

SQL Issue with my query

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?

Upvotes: 0

Views: 28

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

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.

Upvotes: 2

Related Questions