Average Salary: Departments VS Company
Problem
salary(id, employee_id, amount, pay_date) and employee(id, department_id). Per (pay_month, department), label avg dept salary as higher/lower/same vs company avg that month.
sal=[(1,1,9000,'2017-03-31'),(2,2,6000,'2017-03-31')] emp=[(1,1),(2,2)][('2017-03',1,'higher'),('2017-03',2,'lower')]SELECT d.pay_month, d.department_id,
CASE WHEN d.dept_avg > c.comp_avg THEN 'higher'
WHEN d.dept_avg < c.comp_avg THEN 'lower'
ELSE 'same' END AS comparison
FROM (
SELECT DATE_FORMAT(s.pay_date, '%Y-%m') AS pay_month,
e.department_id, AVG(s.amount) AS dept_avg
FROM Salary s JOIN Employee e ON s.employee_id = e.employee_id
GROUP BY pay_month, e.department_id
) d
JOIN (
SELECT DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, AVG(amount) AS comp_avg
FROM Salary
GROUP BY pay_month
) c ON d.pay_month = c.pay_month;Explanation
The goal is to compare each department's average pay against the whole company's average for the same month, and label it higher, lower, or same. The trick is that you need two averages at two different levels of grouping at once.
For each (pay_month, department_id) group we compute AVG(amount) — the department's average that month. Separately we compute the company-wide average for that same month, here written as c.avg_amt, which comes from a join with a per-month company average.
A CASE expression then does the labeling: if the department average is greater than the company average it is higher, if less it is lower, otherwise same. The GROUP BY pay_month, department_id makes sure we produce exactly one labeled row per department per month.
Example: in March there are two payments, 9000 (dept 1) and 6000 (dept 2). The company average is (9000 + 6000) / 2 = 7500. Dept 1's average is 9000 > 7500 so it is higher; dept 2's is 6000 < 7500 so it is lower.