Start up

1
Start up

• Log on to the network
• Start Management Studio
• Connect to Reliant\sql2k5 and your SalesOrders
  database
  
• Start Books Online

2
MIS 431Dr. Steve RossSpring 2007

• Summarizing andGrouping

Material for this lecture is drawn from SQL
Server 2005 Advanced Techniques, SQL Queries for
Mere Mortals, and the professors experience.
3
Aggregate Functions(original set)

• COUNT ()
• COUNT (column)
• COUNT (DISTINCT column)
• SUM (column or expression)
• AVG (column or expression)
• MAX (column or expression)
• MIN (column or expression)

text, numeric or date-type data
numeric-type data only
4
Aggregate Functions(added in SQL Server 2005)

• CHECKSUM_AGG (ALL or column)
• COUNT_BIG ( or column)
• GROUPING (column)
• STDEV (column or expression)
• STDEVP (column or expression)
• VAR (column or expression)
• VARP (column or expression)

text, numeric or date-type data
numeric-type data only
5
Using Aggregate Functions

• A single function
• SELECT MAX(OrderDate) AS LastOrder FROM ORDERS
• Multiple functions
• SELECT MIN(ShipDate-OrderDate) AS QuickestShip,
  MAX(OrderDate) AS LastOrder FROM ORDERS

6
Restrictions when usingAggregate Functions

• All expressions in SELECT clause must be
  constants or aggregate functions
  
• May not embed one aggregate function in another
• May not use a subquery as the value expression of
  an aggregate function
  

7
Using Aggregate Functionsin Filters

• Place aggregate function in a subquery
• Use with , , , operators
• SELECT ProductName FROM ProductsWHERE
  RetailPrice (SELECT AVG(RetailPrice) FROM
  Products)

8
Practical Exercise 12

• An ER Diagram for SalesOrders appears on page
  477
  
• Using your copy of SalesOrders
• List each product and the vendors whose wholesale
  price is less than or equal to the average
  wholesale price for that product
  

9
Grouping

• Used to compute aggregate statistics (e.g.,
  subtotals) for groups of data
  
• All expressions in SELECT clause must be grouping
  fields, constants, or aggregate functions
  
• SELECT C.CategoryDescription, MAX(P.RetailPrice)
  AS MaxInCategory FROM Categories C INNER JOIN
  Products P ON C.CategoryID P.CategoryIDGROUP
  BY C.CategoryDescription

A grouping field is a field used in the GROUP
BY clause
10
Computed Expressions and Grouping

• When the SELECT clause contains computed
  expressions (e.g., concatenation of FirstName and
  LastName)
  
• The GROUP BY clause must contain the fields used
  in the concatenation (e.g., FirstName and
  LastName)
  
• SELECT LastName ', ' FirstName AS
  CustomerName,MAX(OrderDate) AS
  MostRecentOrderFROM Customers C INNER JOIN
  Orders O ON C.CustomerID O.CustomerIDGROUP BY
  LastName, FirstName

11
Practical Exercise 12

• An ER Diagram for SalesOrders appears on page
  477
  
• Using your copy of SalesOrders
• List the details of each order, including
  customer name (concatenated), and the dollar
  amount of that order
  

12
Filtering Grouped Data

• HAVING clause works on rows after they have been
  grouped
  
• Can be applied to columns named in GROUP BY
  clause or an aggregate function

13
WHERE or HAVING?

• WHERE is evaluated before grouping
• Use to eliminate rows that are of no interest
• Makes grouping more efficient
• HAVING is evaluated after grouping
• Use to eliminate groups that are of no interest

14
The HAVING COUNT Trap

• HAVING applies to groups that have some members
• It wont catch a group that has zero members
• If the query is supposed to return groups that
  have less than x, and some groups might have 0
  (zero), then HAVING clause cannot be used.
  
• See Hernandez and Viescas, pp. 448-453, for a
  solution

15
Practical Exercise 14

• An ER Diagram for SalesOrders appears on page
  477
  
• Using your copy of SalesOrders
• List the number and dates of each order that
  contains more than one item, including customer
  name (concatenated), and the dollar amount of
  that order

16
The ROLLUP Operator

• Used when
• There are two or more fields in GROUP BY clause
• There is a hierarchical relationship (e.g., 1M)
• GROUP BY provides summary statistics for each
  unique combination of values in grouping field
  
• WITH ROLLUP provides summary statistics for
  first-named field(s) and entire set
  

17
The ROLLUP Operator and GROUPING Function

• SELECT CategoryDescription, ProductName,MAX(Order
  Date) AS MostRecentOrder,
  
• SUM(QuantityOrdered) AS NumberSold,
  SUM(QuantityOrderedRetailPrice) AS SalesAmount,
  
• GROUPING(ProductName) AS ProductGroup,
• GROUPING(CategoryDescription) AS CategoryGroup
• FROM dbo.Categories C INNER JOIN dbo.Products P
  ON P.CategoryIDC.CategoryID INNER JOIN
  dbo.Order_Details OD ON OD.ProductNumberP.Product
  Number INNER JOIN dbo.Orders O ON
  O.OrderNumberOD.OrderNumber
• GROUP BY CategoryDescription, ProductName
• WITH ROLLUP

See page 103 of Applied Techniques for method
using CASE GROUPING
18
The ROLLUP Operator

• CategoryDescription ProductName
  MostRecentOrder NumberSold
  SalesAmount ProductGroup CategoryGroup
  
• ---------------------- ---------------------------
  - ----------------------- -----------
  ------------ ------------ -------------
  
• Accessories Clear Shade 85-T Glasses
  1999-12-28 000000.000 330 14850.00
  0 0
  
• Accessories Cycle-Doc Pro Repair Stand
  1999-12-28 000000.000 379 62914.00
  0 0
  
• Accessories Dog Ear Aero-Flow Floor
  Pump 1999-12-28 000000.000 666
  36630.00 0 0
  
• Accessories Dog Ear Cyclecomputer
  1999-11-30 000000.000 30 2250.00
  0 0
  
• Accessories Dog Ear Helmet Mount
  Mirrors 1999-12-26 000000.000 105
  782.25 0 0
  
• Accessories Dog Ear Monster Grip
  Gloves 1999-12-28 000000.000 188
  2820.00 0 0
  
• Accessories Glide-O-Matic Cycling
  Helmet 1999-12-28 000000.000 481
  60125.00 0 0
  
• Accessories HP Deluxe Panniers
  1999-12-26 000000.000 417 16263.00
  0 0
  
• Accessories King Cobra Helmet
  1999-12-28 000000.000 454 63106.00
  0 0
  
• Accessories NULL
  1999-12-28 000000.000 5544 385330.25
  1 0
  
• Bikes Eagle FS-3 Mountain Bike
  1999-12-22 000000.000 72 129600.00
  0 0
  
• Bikes GT RTS-2 Mountain Bike
  1999-12-28 000000.000 791
  1305150.00 0 0
  
• Bikes Trek 9000 Mountain Bike
  1999-12-28 000000.000 2198
  2637600.00 0 0
  
• Bikes Viscount Mountain Bike
  1999-12-28 000000.000 728 462280.00
  0 0
  
• Bikes NULL
  1999-12-28 000000.000 3789
  4534630.00 1 0
  
• Car racks Road Warrior Hitch Pack
  1999-12-28 000000.000 373 65275.00
  0 0
  

19
The CUBE Operator

• Used when
• There are two or more fields in GROUP BY clause
• There is a many-to-many relationship
• GROUP BY provides summary statistics for each
  unique combination of values in grouping field
  
• WITH CUBE provides summary statistics for each
  field and entire set
  

20
The CUBE Operator

• SELECT ProductName, CustLastName,MAX(OrderDate)
  AS MostRecentOrder,
  
• SUM(QuantityOrdered) AS NumberSold,
  SUM(QuantityOrderedRetailPrice) AS SalesAmount
  
• FROM dbo.Products P INNER JOIN dbo.Order_Details
  OD ON OD.ProductNumberP.ProductNumber INNER JOIN
  dbo.Orders O ON O.OrderNumberOD.OrderNumber
  INNER JOIN dbo.Customers C ON C.CustomerIDO.Cust
  omerID
• GROUP BY ProductName,CustLastName
• WITH CUBE

21
The CUBE Operator

• ProductName CustLastName MostRecentOrder NumberSo
  ld SalesAmount
  
• AeroFlo ATB Wheels Bonnicksen 1999-12-11
  000000.000 30 5670.00
  
• AeroFlo ATB Wheels Buchanan 1999-12-27
  000000.000 61 11529.00
  
• AeroFlo ATB Wheels Callahan 1999-11-19
  000000.000 15 2835.00
  
• AeroFlo ATB Wheels Davis 1999-12-04
  000000.000 10 1890.00
  
• AeroFlo ATB Wheels NULL 1999-12-28
  000000.000 406 76734.00
  
• Clear Shade 85-T Glasses Bonnicksen 1999-11-23
  000000.000 18 810.00
  
• Clear Shade 85-T Glasses Viescas 1999-12-08
  000000.000 42 1890.00
  
• Clear Shade 85-T Glasses NULL 1999-12-28
  000000.000 330 14850.00
  
• X-Pro All Weather Tires Bonnicksen 1999-10-31
  000000.000 13 312.00
  
• X-Pro All Weather Tires NULL 1999-12-27
  000000.000 295 7080.00
  
• NULL NULL 2006-04-27 125927.283 14918 5382910.
  07
  
• NULL Bonnicksen 1999-12-28 000000.000 581 2139
  92.46
  
• NULL Buchanan 1999-12-27 000000.000 1371 52956
  0.22
  
• NULL Callahan 1999-12-28 000000.000 1036 35951
  9.39
  

22
Practical Exercise 15

• An ER Diagram for SalesOrders appears on page
  477
  
• Using your copy of SalesOrders
• Compute total sales by employee and by product

23
Next Lecture

• Modifying Data