why do we need group by command at the end of this query??
select t1.emp_no, t1.first_name, t1.last_name, max(t2.salary)- min(t2.salary) as salar_diff,
case
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;
case
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;
1 answers ( 0 marked as helpful)
Hi Kane!
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.
Best,
Martin
Best,
Martin