Case Statement Assignment Task 2
/*Extract a dataset containing the following information about the managers: employee number, first name, and last name. Add two columns at the end – one showing the difference between the maximum and minimum salary of that employee, and another one saying whether this salary raise was higher than $30,000 or NOT.*/
#My code when run produces a single row output.Can't figure out why..The solution provided in the course makes use of a group by clause. Can I know why a group by clause was a requisite in this case?
#here is my code
SELECT
dm.emp_no,
e.first_name,
e.last_name,
MAX(s.salary) - MIN(s.salary) AS salaryDifference,
CASE
WHEN MAX(s.salary) - MIN(s.salary) > 30000 THEN 'raised by more than 30k'
ELSE 'not raised by higher than 30k'
END AS salaryState
FROM
dept_manager dm
JOIN
employees e ON dm.emp_no = e.emp_no
JOIN
salaries s ON e.emp_no = s.emp_no;
Hi Carlton!
Thanks for reaching out.
You need to use the GROUP BY clause because otherwise your result set will contain only 1 row. This is because when you do not use the GROUP BY clause the first record that matches the conditions is returned.
Hope this helps.
Best,
Tsvetelin
"This is because when you do not use the GROUP BY clause the first record that matches the conditions is returned." Thanks for pointing that out Tsvetelin.
Hi Carlton!
You are very welcome!
Best,
The 365 Team