CODEWITHSUNDEEP
sqlsql-server
Razvan
Razvan

Reputation: 38

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

I'm using SQL Server 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.

Upvotes: 1

Views: 1981

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

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;

EDIT:

If you want to be sure that all departments get updated, then use a LEFT JOIN:

UPDATE d
    SET Score = COALESCE(de.total, 0)  -- only needed if you don't want `NULL`s
    FROM Departments d LEFT 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;

Upvotes: 1

Ali Adlavaran
Ali Adlavaran

Reputation: 3735

It should be like this:

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

I hope to be helpful for you:)

Upvotes: 0

Related Questions