How do I select the workers who make more money than their leader within their respective department in sql?

user_14496584 0
Viewed 30

I have the following table:

+---------------------------------------------------+
| ID EMPNAME DEPARTMENT ROLE       SALARY   EXTRA   |
| 1  brian   10         sales      2000     500     |
| 2  jenny   10         leader     3000     100     |
| 3  sam     20         office     1500     500     |
| 4  cory    20         leader     1200     300     |
| 5  tifa    30         custservice4000     1000    |
| 6  sammy   30         leader     3000     200     |
| 7 anne     30         sales      4500     200     |
+---------------------------------------------------+

My job is to find the people who make more than their leader (salary + extra) within each role. I will then display the id, empname, and department of that person. I need a way compare the salaries + extra (extra is the bonus they make each year) between roles within each department. I have tried something like the following code but it didn't work, it wasn't calculating the highest vs the leader properly.

SELECT a.id, a.empname, a.department
FROM MyTable a
JOIN (SELECT MAX(Salary + extra) As Highest, Dept FROM MyTable GROUP BY Dept) b
ON a.Dept = b.Dept && (a.Salary + a.extra) = b.Highest

How do I display the first three columns on the criteria that they make more than their leader in their specific department? The above table should output the following below:

+-----------------------+
| ID EMPNAME DEPARTMENT |
| 3  sam     20         |
| 5  tifa    30         |
| 7  anne    30         |
+-----------------------+

This is done in sql developer.

EDIT: Forgot to add that some values of EXTRA may be NULL.

select mysql sql
2 Answers

Hmmm . . . I'm thinking a correlated subquery:

select t.*
from mytable t
where (t.salary + t.extra) > (select t2.salary + t2.extra
                              from mytable t2
                              where t2.department = t.department and
                                    t2.role = 'leader'
                             );
user_11440350

You could use a join approach here:

SELECT mt1.id, mt1.empname, mt1.department
FROM MyTable mt1
INNER JOIN
(
    SELECT department, salary + COALESCE(extra, 0) AS total
    FROM MyTable
    WHERE ROLE = 'leader'
) mt2
    ON mt1.department = mt2.department
WHERE
    mt1.salary + COALESCE(mt1.extra, 0) > mt2.total;
user_18632290
Related