Transcript No Slide Title

Chapter 5
How to code
summary queries
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
 Code SELECT statements that require any of the language
elements presented in this chapter.
Knowledge
 Describe summary queries.
 Describe the differences between the HAVING clause and the
WHERE clause.
 Describe the use of the WITH ROLLUP and WITH CUBE
operators.
 Describe the use of the GROUPING SETS operator.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 2
The syntax of the aggregate functions
Function syntax
AVG([ALL|DISTINCT] expression)
SUM([ALL|DISTINCT] expression)
MIN([ALL|DISTINCT] expression)
MAX([ALL|DISTINCT] expression)
COUNT([ALL|DISTINCT] expression)
COUNT(*)
Murach’s SQL Server 2008, C5
Result
The average of the non-null
values in the expression.
The total of the non-null
values in the expression.
The lowest non-null value in
the expression.
The highest non-null value in
the expression.
The number of non-null
values in the expression.
The number of rows selected
by the query.
© 2008, Mike Murach & Associates, Inc.
Slide 3
How to code aggregate functions
 Aggregate functions, also called column functions, perform a
calculation on the values in a set of selected rows.
 You specify the values for the calculation by coding an
expression for the function’s argument. Often, the expression is
just the name of a column.
 By default, all values are included in the calculation regardless of
whether they’re duplicated. To omit duplicate values, code the
DISTINCT keyword.
 All of the aggregate functions except for COUNT(*) ignore null
values.
 If you code an aggregate function in the SELECT clause, that
clause can’t include non-aggregate columns from the base table.
 A SELECT statement that includes an aggregate function can be
called a summary query.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 4
A summary query that counts unpaid invoices
and calculates the total due
SELECT COUNT(*) AS NumberOfInvoices,
SUM(InvoiceTotal - PaymentTotal - CreditTotal)
AS TotalDue
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
The result set
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 5
A summary query that uses the COUNT(*), AVG,
and SUM functions
SELECT 'After 1/1/2008' AS SelectionDate,
COUNT(*) AS NumberOfInvoices,
AVG(InvoiceTotal) AS AverageInvoiceAmount,
SUM(InvoiceTotal) AS TotalInvoiceAmount
FROM Invoices
WHERE InvoiceDate > '2008-01-01'
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 6
A summary query that uses the MIN and MAX
functions
SELECT 'After 1/1/2008' AS SelectionDate,
COUNT(*) AS NumberOfInvoices,
MAX(InvoiceTotal) AS HighestInvoiceTotal,
MIN(InvoiceTotal) AS LowestInvoiceTotal
FROM Invoices
WHERE InvoiceDate > '2008-01-01'
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 7
A summary query that works on non-numeric
columns
SELECT MIN(VendorName) AS FirstVendor,
MAX(VendorName) AS LastVendor,
COUNT(VendorName) AS NumberOfVendors
FROM Vendors
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 8
A summary query that uses the DISTINCT
keyword
SELECT COUNT(DISTINCT VendorID) AS NumberOfVendors,
COUNT(VendorID) AS NumberOfInvoices,
AVG(InvoiceTotal) AS AverageInvoiceAmount,
SUM(InvoiceTotal) AS TotalInvoiceAmount
FROM Invoices
WHERE InvoiceDate > '2008-01-01'
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 9
The syntax of the SELECT statement
with the GROUP BY and HAVING clauses
SELECT select_list
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_list]
[HAVING search_condition]
[ORDER BY order_by_list]
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 10
How to code the GROUP BY and HAVING clauses
 The GROUP BY clause groups the rows of a result set based on
one or more columns or expressions.
 A group-by list consists of one or more columns or expressions,
separated by commas. It can’t contain aggregate functions.
 When a GROUP BY clause is used, the SELECT clause can
include aggregate functions, the columns used for grouping, and
expressions that result in a constant value.
 If you include aggregate functions in the SELECT clause, the
aggregate is calculated for each set of values that result from the
columns named in the GROUP BY clause.
 If you include multiple items in the group-by list, they form a
hierarchy where each item is subordinate to the previous one.
 The HAVING clause specifies a search condition for a group or an
aggregate.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 11
A summary query that calculates the
average invoice amount by vendor
SELECT VendorID,
AVG(InvoiceTotal) AS AverageInvoiceAmount
FROM Invoices
GROUP BY VendorID
HAVING AVG(InvoiceTotal) > 2000
ORDER BY AverageInvoiceAmount DESC
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 12
A summary query that counts the
number of invoices by vendor
SELECT VendorID, COUNT(*) AS InvoiceQty
FROM Invoices
GROUP BY VendorID
(34 rows)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 13
A summary query that calculates the number of
invoices and the average invoice amount for the
vendors in each state and city
SELECT VendorState, VendorCity, COUNT(*) AS InvoiceQty,
AVG(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
GROUP BY VendorState, VendorCity
ORDER BY VendorState, VendorCity
(20 rows)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 14
A summary query that limits the groups to those
with two or more invoices
SELECT VendorState, VendorCity, COUNT(*) AS InvoiceQty,
AVG(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
GROUP BY VendorState, VendorCity
HAVING COUNT(*) >= 2
ORDER BY VendorState, VendorCity
(12 rows)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 15
How the HAVING clause compares to the WHERE
clause
 When you include a WHERE clause in a query that uses grouping
and aggregates, the search condition is applied first. So only the
rows that satisfy the condition are grouped and summarized.
 When you include a HAVING clause in a query that uses
grouping and aggregates, the search condition is applied last. So
only the groups that satisfy the condition are included in the result
set.
 A HAVING clause must refer to a column that’s included in the
SELECT clause. A WHERE clause can refer to any column in the
base tables.
 Aggregate functions can only be coded in the HAVING clause. A
WHERE clause can’t contain aggregate functions.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 16
A summary query with a search condition in the
HAVING clause
SELECT VendorName, COUNT(*) AS InvoiceQty,
AVG(InvoiceTotal) AS InvoiceAvg
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
HAVING AVG(InvoiceTotal) > 500
ORDER BY InvoiceQty DESC
(19 rows)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 17
A summary query with a search condition in the
WHERE clause
SELECT VendorName, COUNT(*) AS InvoiceQty,
AVG(InvoiceTotal) AS InvoiceAvg
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal > 500
GROUP BY VendorName
ORDER BY InvoiceQty DESC
(20 rows)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 18
How to code complex search conditions in the
HAVING clause
 You can use the AND and OR operators to code compound
search conditions in a HAVING clause just as you can in a
WHERE clause.
 In most cases, your code will be easier to read if you code all the
search conditions in the HAVING clause. But you can code nonaggregate search conditions in the WHERE clause, if you prefer.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 19
A summary query with a compound condition in
the HAVING clause
SELECT InvoiceDate, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceSum
FROM Invoices
GROUP BY InvoiceDate
HAVING InvoiceDate BETWEEN '2008-05-01' AND '2008-05-31'
AND COUNT(*) > 1
AND SUM(InvoiceTotal) > 100
ORDER BY InvoiceDate DESC
The same query coded with a WHERE clause
SELECT InvoiceDate, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceSum
FROM Invoices
WHERE InvoiceDate BETWEEN '2008-05-01' AND '2008-05-31'
GROUP BY InvoiceDate
HAVING COUNT(*) > 1
AND SUM(InvoiceTotal) > 100
ORDER BY InvoiceDate DESC
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 20
The result set returned by both queries
(7 rows)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 21
How to use the ROLLUP operator
 You can use the WITH ROLLUP phrase in the GROUP BY
clause to add summary rows to the final result set.
 A summary row is added for each group in the GROUP BY clause
except for the rightmost group.
 A final summary row is also added to summarize the entire result
set. If the GROUP BY clause specifies a single group, only the
final summary row is added.
 In each summary row, a summary is provided for each aggregate
column in the select list. All other columns, except the ones that
identify which group is being summarized, are assigned null
values.
 The sort sequence in the ORDER BY clause is applied after the
summary rows are added. So if you sort grouping columns in
descending sequence, the summary row for each group (which can
contain null values) will appear after the other rows in the group.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 22
A summary query that includes a final summary row
SELECT VendorID, COUNT(*) AS InvoiceCount,
SUM(InvoiceTotal) AS InvoiceTotal
FROM Invoices
GROUP BY VendorID WITH ROLLUP
Another way to code the GROUP BY clause (SQL
Server 2008)
GROUP BY ROLLUP(VendorID)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 23
A summary query that includes a summary row
for each grouping level
SELECT VendorState, VendorCity, COUNT(*) AS QtyVendors
FROM Vendors
WHERE VendorState IN ('IA', 'NJ')
GROUP BY VendorState, VendorCity WITH ROLLUP
ORDER BY VendorState DESC, VendorCity DESC
Another way to code the GROUP BY clause (SQL
Server 2008)
GROUP BY ROLLUP(VendorState, VendorCity)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 24
How to use the CUBE operator
 You can use the WITH CUBE phrase in the GROUP BY clause to
add summary rows to the final result set.
 A summary row is added for every combination of groups in the
GROUP BY clause.
 A final summary row is also added to summarize the entire result
set.
 In each summary row, a summary is provided for each aggregate
column in the select list. All other columns, except the ones that
identify which group is being summarized, are assigned null
values.
 The sort sequence in the ORDER BY clause is applied after the
summary rows are added. So if you sort grouping columns in
descending sequence, the summary row for each group (which can
contain null values) will appear after the other rows in the group.
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 25
A summary query that includes a final summary row
SELECT VendorID, COUNT(*) AS InvoiceCount,
SUM(InvoiceTotal) AS InvoiceTotal
FROM Invoices
GROUP BY VendorID WITH CUBE
Another way to code the GROUP BY clause (SQL
Server 2008)
GROUP BY CUBE(VendorID)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 26
A summary query that includes a summary row
for each set of groups
SELECT VendorState, VendorCity, COUNT(*) AS QtyVendors
FROM Vendors
WHERE VendorState IN ('IA', 'NJ')
GROUP BY VendorState, VendorCity WITH CUBE
ORDER BY VendorState DESC, VendorCity DESC
Another way to code the GROUP BY clause (SQL
Server 2008)
GROUP BY CUBE(VendorState, VendorCity)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 27
A summary query with two grouping sets
(SQL Server 2008)
SELECT VendorState, VendorCity, COUNT(*) AS QtyVendors
FROM Vendors
WHERE VendorState IN ('IA', 'NJ')
GROUP BY GROUPING SETS(VendorState, VendorCity)
ORDER BY VendorState DESC, VendorCity DESC
The result set
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 28
A summary query with a composite grouping
SELECT VendorState, VendorCity, VendorZipCode,
COUNT(*) AS QtyVendors
FROM Vendors
WHERE VendorState IN ('IA', 'NJ')
GROUP BY GROUPING SETS((VendorState, VendorCity),
VendorZipCode, ())
ORDER BY VendorState DESC, VendorCity DESC
The result set
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 29
A grouping set that uses the ROLLUP operator
GROUP BY GROUPING SETS(
ROLLUP(VendorState, VendorCity), VendorZipCode)
Murach’s SQL Server 2008, C5
© 2008, Mike Murach & Associates, Inc.
Slide 30