|
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/
|
