I'm currently learning the basics of SQL and now I'm facing the following problem. The implementation of it will be in PostgreSQL.

Let's say you have the two following tables in your database:

1. department(dpID:PK, ..., avg_salary)
2. employee(eID:PK, ..., dpID:FK REF department, salary)

(PK = Primary Key, FK = Foreign Key)

So every employee has a salary and a department, every department should store the average salary of its employees (not just in a view).

Of course there should be some kind of update rule in the database, so when a new employee gets inserted, the avg_salary of the employees department gets updated / recalculated.

So when there are e.g. currently no employees in a department X, the avg_salary should be 0. After inserting an employee in department X with a salary of 1000, the avg_salary should be 1000.

Calculating the avg_salary wouldn't be a problem with a sql query.

SELECT AVG(e.salary) FROM empoyee e WHERE e.dpID = ...

But I'm stuck at finding a way to use this result to solve my problem.

So I would be very glad for ideas to realize the function of automatically (re-)calculating attribute values with the result of an aggregation function used on a different table.

Thank you and have a nice day! :)

CodePudding user response：

I agree with nbk, you shouldn't use a function to recalculate. What I would do is create a view. Something like this

create view deptview as select d.name, coalesce(avg(salary),0) from employee e right join department d on e.dpId=d.id group by d.name;

It will recalculate the average every time you use the deptview view

To see your data just

select * from deptview;