CODEWITHSUNDEEP
mysqlsql
srp1908
srp1908

Reputation: 55

SQL comparing value to average value for a specific year

Desired output : I want to select all titles of employees who in 1991 had a higher salary than the average in 1991.

Current code : 

USE employees;
SELECT t.title
FROM employees emps INNER JOIN
 employees.salaries s
 ON s.emp_no = emps.emp_no INNER JOIN
 employees.titles t
 ON t.emp_no = s.emp_no
 WHERE s.from_date LIKE '1991%'  AND s.salary >

Employees table:

CREATE TABLE employees (
emp_no      INT             NOT NULL,
birth_date  DATE            NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,    
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no)

Salaries table:

CREATE TABLE salaries (
emp_no      INT             NOT NULL,
salary      INT             NOT NULL,
from_date   DATE            NOT NULL,
to_date     DATE            NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)

Titles table:

CREATE TABLE titles (
emp_no      INT             NOT NULL,
title       VARCHAR(50)     NOT NULL,
from_date   DATE            NOT NULL,
to_date     DATE,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,title, from_date)

The from_date in the salaries table has to be used.

Upvotes: 3

Views: 1007

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

First of all there is time range in the salary table. A person can have worked all 1991 for one salary. That would be the case for instance for a person who worked from 1989 till 2000 for the same salary. But a person can also have worked part of the year for one salary and the other part for another salary (e.g. a salary rise in July). And a person may have started in December 1, 1991. Or quit on January 15, 1991. How do you want to calculate the average?

Let's say you simply count all salary records in 1991. E.g. person A worked in January for 1000 and the rest of the year for 3000, person B worked all year for 1000 and person C only worked in June for 2000 and in July for 4000. The calculation: (1000 + 3000 + 1000 + 3300 + 3700) / 5 = 2400. The persons who worked in 1991 for a higher salary than 2400 are A and C.

select *
from titles 
where emp_no in
(
  select emp_no
  from salaries
  where from_date <= date '1991-12-31' and to_date >= date '1991-01-01'
  and salary >
  (
    select avg(salary)
    from salaries
    where from_date <= date '1991-12-31' and to_date >= date '1991-01-01'
  )
);

You may want to restrict the titles to 1991, too. I don't know. Maybe you even want them to match the time spans where the salary was higher than the average. In that case you'd have to look for overlapping time ranges. (Example 1: Salary rise in June, new title in July would be two titles for the high salary. Example 2: New title in June, salary rise in July would be one title with the high salary.)

If you have MySQL 8.0 you can use a WITH clause to select the 1991 salaries to get the query more readable.

Upvotes: 2

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • In a Derived Table, compute the average salary for the year 1991. You can use Year() function to determine the year from a given MySQL date.
  • Cross Join with the derived table, so that every row gets a avg_salary_in_1991 field, allowing comparisons.
  • Put a condition in where, so that the employee salary should be greater than the computed avg_salary_in_1991.

Try the following:

USE employees;
SELECT t.title
FROM employees AS emps 
INNER JOIN employees.salaries AS s ON s.emp_no = emps.emp_no 
INNER JOIN employees.titles AS t ON t.emp_no = s.emp_no 
CROSS JOIN (SELECT AVG(s2.salary) AS avg_salary_in_1991 
            employees.salaries AS s2 
            WHERE YEAR(s2.from_date) = 1991) AS avg_sal_1991 

 WHERE YEAR(s.from_date) = 1991  AND 
       s.salary > avg_sal_1991.avg_salary_in_1991

Upvotes: 0

Related Questions