Last answered:

20 Jun 2022

Posted on:

11 Jun 2022

0

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;

3 answers ( 0 marked as helpful)
Instructor
Posted on:

13 Jun 2022

1

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

Posted on:

13 Jun 2022

0

"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.

Instructor
Posted on:

20 Jun 2022

0

Hi Carlton!

You are very welcome!

Best,
The 365 Team

Submit an answer