Question :
Given two tables below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.
— Table: salary
— | id | employee_id | amount | pay_date |
---|---|---|---|---|
— | 1 | 1 | 9000 | 2017-03-31 |
— | 2 | 2 | 6000 | 2017-03-31 |
— | 3 | 3 | 10000 | 2017-03-31 |
— | 4 | 1 | 7000 | 2017-02-28 |
— | 5 | 2 | 6000 | 2017-02-28 |
— | 6 | 3 | 8000 | 2017-02-28 |
— The employee_id column refers to the employee_id in the following table employee.
— | employee_id | department_id |
---|---|---|
— | 1 | 1 |
— | 2 | 2 |
— | 3 | 2 |
— So for the sample data above, the result is:
— | pay_month | department_id | comparison |
---|---|---|---|
— | 2017-03 | 1 | higher |
— | 2017-03 | 2 | lower |
— | 2017-02 | 1 | same |
— | 2017-02 | 2 | same |
Below is the solution that I tried –
WITH comp_avg AS (
SELECT
date_format(pay_date, '%Y-%m') AS months,
AVG(amount) AS avg_amt
FROM
salary
GROUP BY
date_format(pay_date, '%Y-%m')
)
SELECT
date_format(s.pay_date, '%Y-%m') AS months,
e.department_id,
AVG(s.amount) AS avg_amount,
CASE
WHEN AVG(s.amount) > (SELECT avg_amt FROM comp_avg WHERE date_format(s.pay_date, '%Y-%m') = comp_avg.months) THEN 'higher'
WHEN AVG(s.amount) < (SELECT avg_amt FROM comp_avg WHERE date_format(s.pay_date, '%Y-%m') = comp_avg.months) THEN 'lower'
ELSE 'same'
END AS comparison
FROM
salary s
JOIN
employee e ON s.employee_id = e.employee_id
GROUP BY
date_format(s.pay_date, '%Y-%m'), e.department_id;
Explanation –
- I first found the year-month wise average salary of the company and stored it in a cte
- Computed year-month and department wise average salary and used the above cte results for that year-month and compared and showed the output.
But am getting the below error –
Expression #4 of SELECT list is not in GROUP BY clause and contains
nonaggregated column ‘mycompiler.s.pay_date’ which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
Am not sure, where am going wrong as the subquery is working perfectly fine when am running it separately.