Module 3:
Grouping and Summarizing
Data
Module 3: Grouping and Summarizing Data
• Summarizing Data by Using Aggregate Functions
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries
Lesson 1: Summarizing Data by Using Aggregate
Functions
• Aggregate Functions Native to SQL Server
• Using Aggregate Functions with NULL Values
Aggregate Functions Native to SQL Server
• Can be used in:
The select list of a SELECT statement
A COMPUTE or COMPUTE BY clause
A HAVING clause
USE AdventureWorks
USE AdventureWorks
SELECT AVG(VacationHours)AS
SELECT COUNT(*)
MAX(TaxRate) SUM
'AverageVacationHours',
(SickLeaveHours) AS 'TotalSickLeaveHours‘
FROM [Link]
[Link]
FROM [Link]
GROUP BY
WHERE TaxType;> 25000;
SalesQuota
WHERE Title LIKE 'Vice President%'
Using Aggregate Functions With NULL Values
• Most aggregate functions ignore NULL values
• NULL values may produce unexpected or incorrect
results
• Use the ISNULL function to correct this issue
USE AdventureWorks
SELECT AVG(ISNULL(Weight,0)) AS ‘AvgWeight’
FROM [Link]
• The COUNT(*) function is an exception and returns
the total number of records in a table
Lesson 2: Summarizing Grouped Data
• Using the GROUP BY clause
• Filtering Grouped Data by Using the HAVING Clause
• Building a Query for Summarizing Grouped Data – GROUP
BY
• Examining How the ROLLUP and CUBE Operators Work
• Using the ROLLUP and CUBE Operators
• Using the COMPUTE and COMPUTE BY Clauses
• Building a Query for Summarizing Grouped Data -
COMPUTE
• Using GROUPING SETS
Using the GROUP BY Clause
• Specifies the groups into which the output rows must
be placed
• Calculates a summary value for aggregate functions
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM [Link]
SalesOrderID SubTotal
GROUP BY SalesOrderID 1 23761
2 45791
ORDER BY SalesOrderID 3 75909
4 19900
Group 1 Group 2
Source
Table
Filtering Grouped Data by Using the HAVING
Clause
• Specifies a search condition for a group
• Can be used only with the SELECT statement
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM [Link] SalesOrderID SubTotal
43875 121761.939600
GROUP BY SalesOrderID 43884 115696.331324
44518 126198.336168
HAVING SUM(LineTotal) > 100000.00 44528 108783.587200
44530 104958.806836
ORDER BY SalesOrderID 44795 104111.515642
46066 100378.907800
...
Building a Query for Summarizing Grouped Data
– GROUP BY
• GROUP BY
SELECT [Link], COUNT(E. BusinessEntityID) EmployeeCount
FROM [Link] E City EmployeeCount
Bellevue 35
INNER JOIN [Link] A ON [Link] =
Berlin 1
[Link] Bordeaux 1
Bothell 22
GROUP BY [Link] ORDER BY [Link];
Calgary 1
Cambridge 2
• GROUP BY with HAVING clause ...
SELECT DATEPART(yyyy,OrderDate) AS 'Year' ,SUM(TotalDue)
AS
'Total Order Amount'
FROM [Link] Year Total Order Amount
GROUP BY DATEPART(yyyy,OrderDate) 2003 54307615.0868
2004 32196912.4165
HAVING DATEPART(yyyy,OrderDate) >= '2003'
ORDER BY DATEPART(yyyy,OrderDate)
Examining How the ROLLUP and CUBE Operators
Work
• ROLLUP and CUBE generate summary information in a
query
• ROLLUP generates a result set showing the aggregates
for a hierarchy of values in selected columns
SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c)
• CUBE generates a result set that shows the aggregates
for all combination of values in selected columns
SELECT a, b, c, SUM (<expression>)
FROM T
GROUP BY CUBE (a,b,c)
Using the ROLLUP and CUBE Operators
SELECT ProductID,Shelf,SUM(Quantity) AS QtySum ProductID Shelf QtySum
1 A 761
FROM [Link]
1 B 324
WHERE ProductID<6 1 NULL 1085
2 A 791
GROUP BY ROLLUP(ProductID,Shelf)
2 B 318
2 NULL 1109
3 A 909
3 B 443
3 NULL 1352
4 A 900
ProductID Shelf QtySum
1 A 761
2 A 791
SELECT ProductID,Shelf,SUM(Quantity) AS QtySum
3 A 909
FROM [Link] 4 A 900
NULL A 3361
WHERE ProductID<6
1 B 324
GROUP BY CUBE(ProductID, Shelf) 2 B 318
3 B 443
4 B 442
NULL B 1507