SQL: Replace NULL values in Salary column with average salary of the corresponding department

0
votes

I have employees table with columns name, salary and department. I need to impute the NULL values in the salary column with average salary value in their corresponding department. How to do it in SQL?

Suppose if a particular employee salary value is NULL and that employee belongs to X dept. The code should replace the NULL value in the salary column with the 'average salary of employees in X dept'

Table:

----------------------------
Name | Salary | Department |
----------------------------
Abhi | 30,000 | Physics    |
Suri | 50,000 | Geology    |
Ajay | 70,000 | Physics    |
Anu  | 30,000 | Physics    |
Ajit | 25,000 | Geology    |
Banu | 20,000 | Electronics|
Sanju| 15,000 | Electronics|
Taman|NULL    | Physics    |

Now Taman's salary is NULL and he belongs to Physics department. We need to replace the NULL value with the average salary in Physics department, that is 43,333.3 The average in Physics dept is taken as (30K + 70K + 30K)/3.

Result:

----------------------------
Name | Salary | Department |
----------------------------
Abhi | 30,000 | Physics    |
Suri | 50,000 | Geology    |
Ajay | 70,000 | Physics    |
Anu  | 30,000 | Physics    |
Ajit | 25,000 | Geology    |
Banu | 20,000 | Electronics|
Sanju| 15,000 | Electronics|
Taman|43.333.3| Physics    |
3
sql
Provide schemas, sample data, expected and actual results, as well as what you have tried. - drum
Are you using MySQL database, SQL Server or something else as your database? Also, do you want to update the data or are you OK with just a SQL statement or a view to give you transformed data? - zedfoxus
I want to update the values in the table, not just view in result set. Mysql or SQL server both are fine. I have also provided sample data and expected result. - bhargav

3 Answers

1
votes

MySQL

You can use something like this for MySQL (assuming your table is called test100):

update test100
inner join (
    select department, avg(salary) as avgsalary
    from test100
    group by department
) b on test100.department = b.department
set salary = b.avgsalary
where salary is null;

See a working example here: https://rextester.com/MPJCPT8601. Adjust as per your needs.

SQL Server

Try this for SQL Server:

update test100
set salary = b.avgsalary
from test100 a
inner join (
    select department, avg(salary) as avgsalary
    from test100
    group by department
) b on a.department = b.department
where salary is null;

Working example here: https://rextester.com/WPCKP58120

Explanation

We create a subquery (aliased as b) that gets averages for each department. Where we find salary to be NULL, we replace it with the average for that department using average salary from that matching department.

0
votes

Something like this should work, although i haven't tested it

SELECT
    name, 
    COALESCE(
        salary, 
        (SELECT AVG(salary) FROM employees b WHERE a.department == b.department)
    ), 
    department 
FROM employees a;
0
votes

You can simply update the table using the following query:

UPDATE TEST T
SET SALARY = ( SELECT AVG(SALARY) FROM TEST TIN
WHERE T.Department = TIN.Department)
WHERE SALARY IS NULL;

AVG will exclude any null data and hence you will be able to achieve the desired result by just using AVG on a particular department. GROUP BY will also not be needed.

Cheers!!