|
Execute the following Microsoft SQL Server T-SQL example scripts to demonstrate how to perform a table UPDATE with the results of a GROUP BY query.
-- SQL GROUP BY Statement Quick Syntax - SQL group by count - group by clause
SELECT Color = COALESCE(Color,'N/A'), -- grouping column
ColorCount = COUNT(* ), -- aggregate function
AvgListPrice = AVG(ListPrice) -- aggregate function
FROM AdventureWorks2008.Production.Product
GROUP BY Color
ORDER BY Color
/* Color ColorCount AvgListPrice
Black 93 725.121
Blue 26 923.6792
Grey 1 125.00
………
*/
------------
------------
-- TSQL UPDATE table with GROUP BY Quick Syntax
------------
-- SQL GROUP BY in Update Statement - update using group by & aggregate function
-- SQL select into table create for demo - T-SQL update query with GROUP BY
SELECT DISTINCT YEAR=YEAR(OrderDate), Sales=CONVERT(money,0.0)
INTO tempdb.dbo.RevenueByYear
FROM AdventureWorks2008.Sales.SalesOrderHeader
GO
UPDATE rby SET rby.Sales = g.Total -- update using table aliases in FROM clause
FROM tempdb.dbo.RevenueByYear rby
INNER JOIN ( SELECT YEAR=YEAR(OrderDate), Total=SUM(TotalDue) -- SUM aggregate
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate) ) g -- GROUP BY on year aliased as "g"
ON rby.Year = g.YEAR
GO
SELECT *, SalesCurrency='$'+CONVERT(varchar,Sales,1) FROM tempdb.dbo.RevenueByYear
ORDER BY YEAR
/*
YEAR Sales SalesCurrency
2001 14327552.2263 $14,327,552.23
2002 39875505.095 $39,875,505.10
2003 54307615.0868 $54,307,615.09
2004 32196912.4165 $32,196,912.42
*/
DROP TABLE tempdb.dbo.RevenueByYear
GO
------------
USE tempdb;
SELECT Color=ISNULL(Color,'N/A'), ItemCount=0 INTO ProductColor
FROM AdventureWorks2008.Production.Product
GROUP BY Color
GO
-- TSQL update from group by - SQL update table from group by syntax
UPDATE pc
SET pc.ItemCount = cg.ProductColorCount
FROM ProductColor pc
INNER JOIN (SELECT Color=ISNULL(Color,'N/A'), ProductColorCount=COUNT(*)
FROM AdventureWorks2008.Production.Product
GROUP BY Color) cg
ON pc.Color = cg.Color
GO
SELECT * FROM ProductColor
GO
/*
Color ItemCount
N/A 248
Black 93
Blue 26
Grey 1
Multi 8
Red 38
Silver 43
Silver/Black 7
White 4
Yellow 36
*/
DROP TABLE tempdb.dbo.ProductColor
GO
------------
-- T-SQL group by syntax - group by sum aggregate - group by crosstab query
SELECT YEAR = YEAR(OrderDate),
COALESCE(CONVERT(VARCHAR,SUM(CASE
WHEN DATEPART(QQ,OrderDate) = 1 THEN TotalDue
END),1),'') AS 'Quarter 1',
COALESCE(CONVERT(VARCHAR,SUM(CASE
WHEN DATEPART(QQ, OrderDate) = 2 THEN TotalDue
END),1),'') AS 'Quarter 2',
COALESCE(CONVERT(VARCHAR,SUM(CASE
WHEN DATEPART(QQ, OrderDate) = 3 THEN TotalDue
END),1),'') AS 'Quarter 3',
COALESCE(CONVERT(VARCHAR,SUM(CASE
WHEN DATEPART(QQ, OrderDate) = 4 THEN TotalDue
END),1),'') AS 'Quarter 4'
FROM AdventureWorks2008.Sales.SalesOrderHeader soh
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate)
/* Query results
YEAR Quarter 1 Quarter 2 Quarter 3 Quarter 4
2001 5,850,932.95 8,476,619.28
2002 7,379,686.31 8,210,285.17 13,458,206.13 10,827,327.49
2003 8,550,831.87 10,749,269.37 18,220,131.53 16,787,382.31
2004 14,170,982.55 17,969,750.95 56,178.92
*/
------------
In the following section, four UPDATE with GROUP BY methods are presented:
derived table, correlated subquery, CTE and subselect.
USE AdventureWorks
GO
-- FIRST SOLUTION: update with group by using a DERIVED TABLE- update from a Group By Clause
-- Add a new column to table
ALTER TABLE HumanResources.Department
ADD NoOfEmployees int null
GO
-- Update the new column from the GROUP BY derived query DG
-- DG JOINed with the update table - update group clause
-- Inner join group by - sql server update group by - SQL update using join
-- SQL update group by - MSSQL update table with group by
UPDATE DE
SET NoOfEmployees =DG.DeptEmployees
FROM HumanResources.Department DE
INNER JOIN (SELECT D.DepartmentID, COUNT(*) 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.EmployeeID = E.EmployeeID
GROUP BY D.DepartmentID ) DG
ON DE.DepartmentID = DG.DepartmentID
GO
-- Check results
SELECT * FROM HumanResources.Department
GO
-- Cleanup - remove column from table
ALTER TABLE HumanResources.Department
DROP COLUMN NoOfEmployees
GO
-- SECOND SOLUTION: update with group by applying CORRELATED SUBQUERY
-- Add a new column to table
ALTER TABLE HumanResources.Department
ADD NoOfEmployees int null
GO
-- Update the new column from the GROUP BY correlated subquery
-- the WHERE clause defines the correlation
-- SQL group by within update
UPDATE DE
SET NoOfEmployees = (SELECT COUNT(*) 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.EmployeeID = E.EmployeeID
WHERE DE.DepartmentID = D.DepartmentID
GROUP BY D.DepartmentID )
FROM HumanResources.Department DE
GO
-- Check results
SELECT * FROM HumanResources.Department
GO
-- Cleanup - remove just-added column
ALTER TABLE HumanResources.Department
DROP COLUMN NoOfEmployees
GO
------------
-- SQL update group by example for PO and Sales Order counts by year
------------
USE tempdb;
GO
CREATE TABLE AWSummary (
ProfitCenterID int,
FiscalYear int,
POs int,
PODetails int,
SalesOrders int,
SalesOrderDetails int
)
GO
-- SQL insert with select
-- The SELECT uses two group by derived tables
-- Profit Center 1 is the entire company
-- The JOIN assumption is that the [Year]-s are matching
-- SQL inner join group by
INSERT AWSummary (ProfitCenterID, FiscalYear, POs, SalesOrders)
SELECT 1, po.[Year], POCount, SOCount
FROM
(
SELECT [Year]=YEAR(OrderDate), POCount=COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GROUP BY YEAR(OrderDate)
) po
INNER JOIN
(
SELECT [Year]=YEAR(OrderDate), SOCount=COUNT(*)
FROM AdventureWorks.Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
) so
ON po.[Year] = so.[Year]
ORDER BY po.[Year]
GO
-- SQL update with group by for Purchase Orders Detail
-- SQL derived table join is used for the update
UPDATE AWSummary
SET PODetails = Details
FROM AWSummary s
INNER JOIN
(SELECT [Year]= YEAR(OrderDate), Details= COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader poh
INNER JOIN AdventureWorks.Purchasing.PurchaseOrderDetail pod
on poh.PurchaseOrderID = pod.PurchaseOrderID
GROUP BY YEAR(OrderDate)
) d
ON d.Year = s.FiscalYear
WHERE s.ProfitCenterID = 1
GO
-- SQL update with group by for Sales Orders Detail
-- SQL derived table is used for the update
-- SQL update with join
UPDATE AWSummary
SET SalesOrderDetails = Details
FROM AWSummary s
INNER JOIN
(SELECT [Year]= YEAR(OrderDate), Details= COUNT(*)
FROM AdventureWorks.Sales.SalesOrderHeader soh
INNER JOIN AdventureWorks.Sales.SalesOrderDetail sod
on soh.SalesOrderID = sod.SalesOrderID
GROUP BY YEAR(OrderDate)
) d
ON d.Year = s.FiscalYear
WHERE s.ProfitCenterID = 1
GO
-- Check results
SELECT FiscalYear, POs, PODetails, SalesOrders, SalesOrderDetails
FROM AWSummary
WHERE ProfitCenterID=1
ORDER BY FiscalYear
GO
/* Results
FiscalYear POs PODetails SalesOrders SalesOrderDetails
2001 8 15 1379 5151
2002 272 629 3692 19353
2003 1035 2344 12443 51237
2004 2697 5857 13951 45576
*/
-- Cleanup
DROP TABLE AWSummary
GO
------------
-- THIRD SOLUTION: update group by using a CTE - Common Table Expression
ALTER TABLE HumanResources.Department
ADD NoOfEmployees int null
GO
-- Update the new column from the GROUP BY cte
-- cte JOINed with the update table
-- SQL Server update group by
;WITH cteDeptCount AS
(SELECT D.DepartmentID, COUNT(*) 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.EmployeeID = E.EmployeeID
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 * FROM HumanResources.Department
GO
-- Cleanup - remove just-added column
ALTER TABLE HumanResources.Department
DROP COLUMN NoOfEmployees
GO
------------
-- FOURTH SOLUTION: update with SUBSELECT and group by
------------
-- SQL update with subselect and group by
------------
USE tempdb;
-- SQL select into create table for example
SELECT DISTINCT SupervisorName=LastName+', '+FirstName,
SupervisorID=e2.EmployeeID, DirectlySupervise = 0
INTO DirectSupervisor
FROM AdventureWorks.HumanResources.Employee e1
INNER JOIN AdventureWorks.HumanResources.Employee e2
ON e1.ManagerID = e2.EmployeeID
INNER JOIN AdventureWorks.Person.Contact c
ON e2.ContactID = c.ContactID
ORDER BY SupervisorName
GO
-- SELECT * FROM DirectSupervisor
-- (47 row(s) affected)
SELECT TOP 5 * FROM DirectSupervisor ORDER BY NEWID()
GO
/* Partial results
SupervisorName SupervisorID DirectlySupervise
Kleinerman, Christian 49 0
Brown, Jo 16 0
Kim, Shane 159 0
Kane, Lori 197 0
Wright, A. Scott 44 0
*/
-- SQL update with subselect
-- SQL Server update with subselect
UPDATE DirectSupervisor
SET DirectlySupervise = (SELECT COUNT(*)
FROM AdventureWorks.HumanResources.Employee e
WHERE E.ManagerID = DirectSupervisor.SupervisorID)
GO
-- (47 row(s) affected)
SELECT TOP 5 * FROM DirectSupervisor ORDER BY NEWID()
GO
/* Partial results
SupervisorName SupervisorID DirectlySupervise
Krebs, Peter 21 22
Sánchez, Ken 109 6
Barreto de Mattos, Paula 30 5
Bradley, David 6 8
Welcker, Brian 273 3
*/
-- Equivalent update with group by - SQL Server inner join group by
-- SQL Server update with group by
UPDATE dr
SET DirectlySupervise = sc.StaffCount
FROM DirectSupervisor dr
INNER JOIN (SELECT e.ManagerID, StaffCount=COUNT(*)
FROM AdventureWorks.HumanResources.Employee e
GROUP BY ManagerID) sc
ON sc.ManagerID = dr.SupervisorID
GO
-- Cleanup
DROP TABLE tempdb.dbo.DirectSupervisor
GO
------------ |
