SQLUSA
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to update a table with group by sum?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to update the SalesPerson & HumanResources.Department table with a correlated GROUP BY sum aggregates:

USE AdventureWorks2008;

GO

 

UPDATE sp

SET SalesYTD = SalesYTD +

    (SELECT SUM(soh1.SubTotal)

     FROM Sales.SalesOrderHeader AS soh1

     WHERE soh1.OrderDate =

     (SELECT MAX(OrderDate)

      FROM Sales.SalesOrderHeader AS soh2

      WHERE soh2.SalesPersonID = soh1.SalesPersonID)

      AND sp.BusinessEntityID = soh1.SalesPersonID

      GROUP BY soh1.SalesPersonID)

FROM Sales.SalesPerson sp

WHERE Bonus >= $4000;

GO

------------

 

 

-- UPDATE table from GROUP BY results

-- SQL Server add column for testing

USE AdventureWorks2008;

ALTER TABLE HumanResources.Department

ADD NoOfEmployees INT    NULL

GO

 

WITH cteDeptCount

     AS (SELECT   D.DepartmentID,

                  SUM (1) AS DeptEmployees

         FROM     HumanResources.EmployeeDepartmentHistory EDH

                  JOIN HumanResources.Department D

                    ON d.DepartmentID = EDH.DepartmentID

                       AND EDH.EndDate IS NULL

                  JOIN HumanResources.Employee E

                    ON EDH.BusinessEntityID = E.BusinessEntityID

         GROUP BY D.DepartmentID)

UPDATE DE

SET    NoOfEmployees = DC.DeptEmployees

FROM   HumanResources.Department DE

       INNER JOIN cteDeptCount DC

         ON DE.DepartmentID = DC.DepartmentID

GO

 

-- Check results

SELECT DepartmentName = Name, NoOfEmployees

FROM   HumanResources.Department

GO

/*

DepartmentName                NoOfEmployees

Engineering                   6

Tool Design                   4

Sales                         18

Marketing                     9

Purchasing                    12

Research and Development      4

Production                    179

Production Control            6

Human Resources               6

Finance                       10

Information Services          10

Document Control              5

Quality Assurance             6

Facilities and Maintenance    7

Shipping and Receiving        6

Executive                     2

*/

-- Cleanup - remove just-added column

ALTER TABLE HumanResources.Department

DROP COLUMN NoOfEmployees

GO

------------

Related article:

 

http://www.sqlusa.com/bestpractices2005/updatewithgroupby/ 

 

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page