1 / 12

Aggregations

Aggregations. Various Aggregation Functions GROUP BY HAVING. Aggregate Functions. SQL Provides several functions that act on columns to provide aggregates of the values in the column

pegg
Télécharger la présentation

Aggregations

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. Aggregations • Various Aggregation Functions • GROUP BY • HAVING

  2. Aggregate Functions • SQL Provides several functions thatact on columns to provideaggregates of the values in the column • Find them under the Programmability |Functions | System Functions |Aggregate Functions section of the Object Browser

  3. Aggregate Functions (cont.) • Try these two queries (run them together) SELECT Quantity, UnitPrice, Quantity * UnitPrice AS 'Total' FROM [Order Details] WHERE OrderID = 10609 SELECT SUM(Quantity) AS 'Total Units Sold', SUM(Quantity * UnitPrice) AS 'Total Value Sold' FROM [Order Details] WHERE OrderID = 10609

  4. Aggregate Functions (cont.) • Aggregate functions return one row with the aggregate values as the columns • You must use AS to give a name to the aggregate column as it will not have a 'natural' name • Most useful functions • AVG ( ) − COUNT( ) • MIN ( ) − MAX( ) • SUM ( ) • Statistical functions • STDEV ( ) − STDEVP ( ) • VAR ( ) − VARP ( )

  5. Aggregate Functions (cont.) • The argument of each aggregate function is usually a column name • It may be a calculated expressionSUM (Quantity * UnitPrice) AS 'Total Value Sold' • The COUNT ( ) function returns the number of rows in the result set regardless of which column is used • SELECT COUNT (*) AS… is commonly used • When a WHERE clause limits the rows with a criteria the aggregations are performed only on the rows that meet the WHERE criteria • Aggregates can be used in multi-table queries

  6. Aggregate Function Exercises • Find the last OrderID in the DB • Find out what the next ProductID will be without actually adding a product • Find out the value of the inventory on hand (based on sales price) of the discontinued products • Find out the average number of products sold whenever the product Carnarvon Tigers have been sold

  7. Grouping in Aggregate Functions • Without grouping an aggregate query will return just one row SELECT * FROM [Order Details] WHERE OrderID = 10523 SELECT Orders.OrderID, COUNT(*) AS 'Number of Products', SUM(Quantity) AS 'Number of Items', SUM(Quantity * UnitPrice * (1 - Discount)) AS 'Revenue' FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE DatePart(mm, OrderDate) = 5 AND DatePart(yy, OrderDate) = 1997 GROUP BY Orders.OrderID

  8. GROUP BY • GROUP BY in an aggregate query gives one row for each distinct value of the fields in the GROUP BY clause • Multiple items in the GROUP BY list can create multiple levels of grouping • Add ProductID to the GROUP BY clause of the previous queryGROUP BY OrderID, ProductID • Items are grouped by the first item, then by the second within the first, etc.

  9. GROUP BY (cont.) • Any column in the SELECT clause that is not an aggregate expression must be listed in the GROUP BY expression • Run the previous query with the GROUP BY commented out • If you wish to display multiple non-aggregated columns then they all must be listed in the GroupBy • Rewrite the previous query to display not only the OrderID but also the Customer CompanyName • You can apply an ORDER BY clause on an aggregated column when using GROUP BY

  10. GROUP BY Exercises • Find the total value of each product in inventory by category (based on selling price) by showing the CategoryID, the quantity on hand, and the total value • Show the total value of sales by sales rep during May 1997. Show the sales rep's name and value.

  11. HAVING • HAVING acts like a WHERE clause applied to the results of a GROUP BY aggregation • Run this query with and without the last line SELECT Orders.OrderID, COUNT(*) AS 'Number of Products', SUM(Quantity) AS 'Number of Items', SUM(Quantity * UnitPrice * (1 - Discount)) AS 'Revenue' FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE DatePart(mm, OrderDate) = 5 AND DatePart(yy, OrderDate) = 1997 GROUP BY Orders.OrderID HAVING SUM(Quantity) < 10

  12. HAVING Exercises • Management wants to know the names of your best customers. Find the CompanyNames and value purchased for all customers who ordered more than $1,000 in merchandise in May of 1997.

More Related