SQLServerCh05

Chapter 5

How to code
summary queries

Murach's SQL Server 2012, C5

© 2012, 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.
 Describe the use of the OVER clause.

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 2

The syntax of the aggregate functions


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 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 3

A summary query that counts unpaid invoices
and calculates the total due (Truy vấn tính tổng số
hóa đơn chưa thanh toán hết tiền và tổng số tiền
nợ của các hóa đơn đó)
SELECT COUNT(*) AS NumberOfInvoices,
SUM(InvoiceTotal - PaymentTotal - CreditTotal)
AS TotalDue
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 4

A summary query with COUNT(*), AVG, and SUM
SELECT 'After 9/1/2011' AS SelectionDate, COUNT(*) AS
NumberOfInvoices,
AVG(InvoiceTotal) AS AverageInvoiceAmount,
SUM(InvoiceTotal) AS TotalInvoiceAmount
FROM Invoices
WHERE InvoiceDate > '2011-09-01';

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 5

A summary query with MIN and MAX
SELECT 'After 9/1/2011' AS SelectionDate,
COUNT(*) AS NumberOfInvoices,
MAX(InvoiceTotal) AS HighestInvoiceTotal,
MIN(InvoiceTotal) AS LowestInvoiceTotal
FROM Invoices
WHERE InvoiceDate > '2011-09-01';

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 6

A summary query for non-numeric columns
SELECT MIN(VendorName) AS FirstVendor,
MAX(VendorName) AS LastVendor,
COUNT(VendorName) AS NumberOfVendors
FROM Vendors;

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 7

A summary query with 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 > '2011-09-01';

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 8

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]

What can be included in the SELECT clause
when grouping is used
 Aggregate functions
 The columns used for grouping
 Expressions that result in constant values

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 9

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;

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 10

A summary query that counts
the number of invoices by vendor
SELECT VendorID, COUNT(*) AS InvoiceQty
FROM Invoices
GROUP BY VendorID;

The result set

(34 rows)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 11

A summary query that groups by two columns
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;

The result set

(20 rows)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 12

The same summary query with a HAVING clause
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;

The result set

(12 rows)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 13

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;

The result set

(19 rows)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 14

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;

The result set

(20 rows)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 15

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 '2012-01-01' AND '2012-01-31'
AND COUNT(*) > 1
AND SUM(InvoiceTotal) > 100
ORDER BY InvoiceDate DESC;

The result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 16

The same summary query with a WHERE clause
SELECT InvoiceDate, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceSum
FROM Invoices
WHERE InvoiceDate BETWEEN '2012-01-01' AND '2012-01-31'
GROUP BY InvoiceDate
HAVING COUNT(*) > 1
AND SUM(InvoiceTotal) > 100
ORDER BY InvoiceDate DESC;

The same result set

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 17

A summary query with 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 or later)
GROUP BY ROLLUP(VendorID)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 18

A summary query with 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 or later)
GROUP BY ROLLUP(VendorState, VendorCity)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 19

A summary query with 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 or later)
GROUP BY CUBE(VendorID)

Murach's SQL Server 2012, C5

© 2012, Mike Murach & Associates, Inc.

Slide 20