1 / 22

Start up

MIS 431 Dr. Steve Ross Spring 2007. Summarizing and Grouping. Material for this lecture is drawn from SQL Server 2005 Advanced Techniques, SQL Queries for Mere Mortals, and the professor's experience.. 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) .

reid
Download Presentation

Start up

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

    2. MIS 431 Dr. Steve Ross Spring 2007 Summarizing and Grouping

    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) †

    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) ‡

    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 using Aggregate 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 Functions in Filters Place aggregate function in a subquery Use with =, <>, <, >, <=, and >= operators SELECT ProductName FROM Products WHERE 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.CategoryID GROUP BY C.CategoryDescription

    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 MostRecentOrder FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID GROUP 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 won’t 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., 1:M) 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(OrderDate) AS MostRecentOrder, SUM(QuantityOrdered) AS NumberSold, SUM(QuantityOrdered*RetailPrice) AS SalesAmount, GROUPING(ProductName) AS ProductGroup, GROUPING(CategoryDescription) AS CategoryGroup FROM dbo.Categories C INNER JOIN dbo.Products P ON P.CategoryID=C.CategoryID INNER JOIN dbo.Order_Details OD ON OD.ProductNumber=P.ProductNumber INNER JOIN dbo.Orders O ON O.OrderNumber=OD.OrderNumber GROUP BY CategoryDescription, ProductName WITH ROLLUP

    18. The ROLLUP Operator CategoryDescription ProductName MostRecentOrder NumberSold SalesAmount ProductGroup CategoryGroup ---------------------- ---------------------------- ----------------------- ----------- ------------ ------------ ------------- Accessories Clear Shade 85-T Glasses 1999-12-28 00:00:00.000 330 14850.00 0 0 Accessories Cycle-Doc Pro Repair Stand 1999-12-28 00:00:00.000 379 62914.00 0 0 Accessories Dog Ear Aero-Flow Floor Pump 1999-12-28 00:00:00.000 666 36630.00 0 0 Accessories Dog Ear Cyclecomputer 1999-11-30 00:00:00.000 30 2250.00 0 0 Accessories Dog Ear Helmet Mount Mirrors 1999-12-26 00:00:00.000 105 782.25 0 0 Accessories Dog Ear Monster Grip Gloves 1999-12-28 00:00:00.000 188 2820.00 0 0 Accessories Glide-O-Matic Cycling Helmet 1999-12-28 00:00:00.000 481 60125.00 0 0 Accessories HP Deluxe Panniers 1999-12-26 00:00:00.000 417 16263.00 0 0 Accessories King Cobra Helmet 1999-12-28 00:00:00.000 454 63106.00 0 0 … Accessories NULL 1999-12-28 00:00:00.000 5544 385330.25 1 0 Bikes Eagle FS-3 Mountain Bike 1999-12-22 00:00:00.000 72 129600.00 0 0 Bikes GT RTS-2 Mountain Bike 1999-12-28 00:00:00.000 791 1305150.00 0 0 Bikes Trek 9000 Mountain Bike 1999-12-28 00:00:00.000 2198 2637600.00 0 0 Bikes Viscount Mountain Bike 1999-12-28 00:00:00.000 728 462280.00 0 0 Bikes NULL 1999-12-28 00:00:00.000 3789 4534630.00 1 0 Car racks Road Warrior Hitch Pack 1999-12-28 00:00:00.000 373 65275.00 0 0 Car racks Ultimate Export 2G Car Rack 1999-12-28 00:00:00.000 365 65700.00 0 0 Car racks NULL 1999-12-28 00:00:00.000 738 130975.00 1 0 Clothing Kool-Breeze Rocket Top Jersey 1999-12-28 00:00:00.000 303 9696.00 0 0 Clothing StaDry Cycling Pants 1999-12-27 00:00:00.000 258 17802.00 0 0 … Wheels NULL 1999-12-28 00:00:00.000 886 25824.00 1 0 NULL NULL 1999-12-28 00:00:00.000 14906 5382367.37 1 1 (47 row(s) affected)

    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(QuantityOrdered*RetailPrice) AS SalesAmount FROM dbo.Products P INNER JOIN dbo.Order_Details OD ON OD.ProductNumber=P.ProductNumber INNER JOIN dbo.Orders O ON O.OrderNumber=OD.OrderNumber INNER JOIN dbo.Customers C ON C.CustomerID=O.CustomerID GROUP BY ProductName,CustLastName WITH CUBE

    21. The CUBE Operator ProductName CustLastName MostRecentOrder NumberSold SalesAmount AeroFlo ATB Wheels Bonnicksen 1999-12-11 00:00:00.000 30 5670.00 AeroFlo ATB Wheels Buchanan 1999-12-27 00:00:00.000 61 11529.00 AeroFlo ATB Wheels Callahan 1999-11-19 00:00:00.000 15 2835.00 AeroFlo ATB Wheels Davis 1999-12-04 00:00:00.000 10 1890.00 … AeroFlo ATB Wheels NULL 1999-12-28 00:00:00.000 406 76734.00 Clear Shade 85-T Glasses Bonnicksen 1999-11-23 00:00:00.000 18 810.00 … Clear Shade 85-T Glasses Viescas 1999-12-08 00:00:00.000 42 1890.00 Clear Shade 85-T Glasses NULL 1999-12-28 00:00:00.000 330 14850.00 … X-Pro All Weather Tires Bonnicksen 1999-10-31 00:00:00.000 13 312.00 … X-Pro All Weather Tires NULL 1999-12-27 00:00:00.000 295 7080.00 NULL NULL 2006-04-27 12:59:27.283 14918 5382910.07 NULL Bonnicksen 1999-12-28 00:00:00.000 581 213992.46 NULL Buchanan 1999-12-27 00:00:00.000 1371 529560.22 NULL Callahan 1999-12-28 00:00:00.000 1036 359519.39 … NULL Pundt 1999-12-28 00:00:00.000 641 184044.01 NULL Rosales 1999-12-24 00:00:00.000 587 205707.20 NULL Schnebly 1999-12-27 00:00:00.000 350 128566.01 NULL Smith 1999-12-26 00:00:00.000 394 144795.83 NULL Thompson 1999-12-28 00:00:00.000 669 226527.19 NULL Viescas 1999-12-27 00:00:00.000 1127 497530.04

    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

More Related