Razvan Razvan - 3 days ago 7
SQL Question

Updating a column value with the sum of multiple values in multiple tables, for each row

I'm using

MSSQL
, and have the following 3 tables:


  1. Employees
    has the columns:
    Name
    ,
    Sales
    ,
    Bonus1
    ,
    Bonus2

  2. Departments
    has the columns:
    Name
    ,
    Score

  3. DepartmentsMembers
    has the columns:
    Name
    (
    Employees.Name
    ),
    Department (
    Departments.Name
    )



How do I calculate and update the value for the
Score
column for each Department based on the sum of
Sales
+
Bonus1
+
Bonus2
of the employees belonging to that department?

Edit:
Based on Ali Adlavaran's answer I got this query to return the list of results with each department and their calculated Score.

SELECT DepartmentsMembers.Name, SUM(Sales + Bonus1 + Bonus2)
FROM DepartmentsMembers
Department INNER JOIN
DepartmentsMembers ON Departments.Name = DepartmentsMembers.Name INNER JOIN
Employees ON DepartmentsMembers.Name = Employees.Name
WHERE DepartmentsMembers.Name = Departments.Name
GROUP BY DepartmentsMembers.Name


How can I put all that in an update statement, for each department?

Thank you.

Answer Source

First, summarize by the department:

SELECT dm.Department,
       SUM(COALESCE(e.Sales, 0) + COALESCE(e.Bonus1, 0) + COALESCE(e.Bonus2, 0)) as total
  FROM DepartmentsMembers dm INNER JOIN
       Employees e
       ON dm.EmployeeName = e.Name
GROUP BY dm.Department;

Then, you can use this in an update for departments:

UPDATE d
    SET Score = de.total
    FROM Departments d JOIN
         (SELECT dm.Department,
                 SUM(COALESCE(e.Sales, 0) + COALESCE(e.Bonus1, 0) + COALESCE(e.Bonus2, 0)) as total
          FROM DepartmentsMembers dm INNER JOIN
               Employees e
               ON dm.EmployeeName = e.Name
          GROUP BY dm.Department
         ) de
         ON de.Department = d.Name;