select t1.emp_no, t1.first_name, t1.last_name, max(t2.salary)- min(t2.salary) as salar_diff,
when max(t2.salary) – min(t2.salary) > 30000 then ‘salary raised by more than 30000’
else ‘raised less than that amount’
end as salary_gap
from employees t1 join salaries t2 on t1.emp_no = t2.emp_no group by t2.emp_no;
Thanks for reaching out!
In this query, the GROUP BY clause allows us to obtain output per employee number. In other words, if we don’t include it, then MySQL will only display an output for the first employee number as obtained by the SQL optimiser and will disregard the rest. That’s how MySQL will behave by default.
Be adding a GROUP BY clause, we designate that we want the output to be separated by employee number.
Hope this helps.
You are welcome!