MySQL create new column with average value from another column

Question

I'm trying to solve an exercise that asks me to create a new column with the average salary of employees in each department. My DB contains employees and departments table. employees is a list of employees with, among other data, the salary of each person and their department_id. departments contains department_id , department_name etc. I've already used ALTER TABLE to add a column called AVG_SALARY into departments. Now, I'd like to fill it with AVG salary based on salary column from employee table. So far, I came up with a lot of solutions, but neither worked. Some of them were:

UPDATE departments D 
INNER JOIN employees E ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
SET AVG_SALARY = AVG(employees.SALARY);
GROUP BY DEPARTMENT_ID;

RESULT: Error Code: 1111. Invalid use of group function

UPDATE departments D
SET D.AVG_SALARY = 
(SELECT AVG(e.SALARY)
FROM employees E INNER JOIN (SELECT * FROM departments) AS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID);

RESULT: Error Code: 1242. Subquery returns more than 1 row

INSERT INTO departments (DEPARTMENT_ID, AVG_SALARY)
SELECT D.DEPARTMENT_ID, AVG(salary)
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_ID;

RESULT:Error Code: 1364. Field 'DEPARTMENT_NAME' doesn't have a default value

Anyone able to help here?

For a better compression of the problem you can add details of the tables, columns , foreing key and others details important. — Marcos Riveros

nacho nacho · Accepted Answer · 2018-05-17T17:17:54

Try with this one that is a modification of the one you made in second place:

UPDATE departments D
SET D.AVG_SALARY = 
    (SELECT AVG(e.SALARY)
     FROM employees E 
     where  D.DEPARTMENT_ID=E.DEPARTMENT_ID
     GROUP BY E.DEPARTMENT_ID);

MySQL create new column with average value from another column

1 Answers