[ Pobierz całość w formacie PDF ]
.It is possible to use the SUM() function to break the calculationsat each new product ID as demonstrated in the following statement.To do so, you must group theproduct IDs together with the GROUP BY clause.USE NORTHWINDSELECT ProductID,SUM(Quantity * UnitPrice *(1 - Discount)) AS [Total Revenues]FROM [Order Details]GROUP BY ProductIDORDER BY ProductIDThe above statement will produce an output like this one:ProductID Total Revenues1 12788.102 16355.963 3044.04 8567.895 5347.206 7137.07 22044.29As you can see, the SUM() function works in tandem with the GROUP BY clause (when there isone) to produce subtotals.The GROUP BY clause groups all the rows with the same values in thespecified column and forces the aggregate functions to act on each group separately.SQL Server willsort the rows according to the column specified in the GROUP BY clause and start calculating theaggregate functions.Every time it runs into a new group, it prints the result and resets the aggregatefunction(s).If you use the GROUP BY clause in a SQL statement, you must be aware of the following rule:All the fields included in the SELECT list must be either part of an aggregate function or part of the GROUP BY clause.Let s say you want to change the previous statement to display the names of the products, ratherthan their IDs.The following statement will display product names, instead of product IDs.Noticewww.sybex.comCopyright ©2002 SYBEX, Inc., Alameda, CA 2877c20.qxd 11/11/01 4:21 PM Page 902902 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTSthat the ProductName field doesn t appear as an argument to an aggregate function, so it must bepart of the GROUP BY clause.USE NORTHWINDSELECT ProductName,SUM(Quantity * [Order Details].UnitPrice * (1 - Discount))AS [Total Revenues]FROM [Order Details], ProductsWHERE Products.ProductID = [Order Details].ProductIDGROUP BY ProductNameORDER BY ProductNameThese are the first few lines of the output produced by this statement:ProductName Total RevenuesAlice Mutton 32698.38Aniseed Syrup 3044.0Boston Crab Meat 17910.63Camembert Pierrot 46927.48Carnarvon Tigers 29171.87If you omit the GROUP BY clause, the query will return the total revenue generated by all theproducts in the database.You can also combine multiple aggregate functions in the SELECT field.The following state-ment will calculate the units of products sold, along with the revenue they generated and the numberof invoices that contain the specific product:USE NORTHWINDSELECT ProductID AS PRODUCT,COUNT(ProductID) AS [INVOICES],SUM(Quantity) AS [UNITS SOLD],SUM(Quantity * UnitPrice *(1 - Discount)) AS RevenueFROM [Order Details]GROUP BY ProductIDORDER BY ProductIDThe following SELECT statement returns all product IDs along with the number of invoicesthat contain them, and the minimum, maximum, and average quantity ordered:USE NORTHWINDSELECT ProductID AS PRODUCT,COUNT(ProductID) AS [Invoices],MIN(Quantity) AS [Min],MAX(Quantity) AS [Max],AVG(Quantity) AS [Average]FROM [Order Details]GROUP BY ProductIDORDER BY ProductIDwww.sybex.comCopyright ©2002 SYBEX, Inc., Alameda, CA 2877c20.qxd 11/11/01 4:21 PM Page 903STRUCTURED QUERY LANGUAGE 903Limiting Groups with HAVINGThe HAVING clause limits the groups that will appear in the cursor.In a way, it is similar to theWHERE clause, but the HAVING clause allows you to use aggregate functions.The followingstatement will return the IDs of the products whose sales exceed 1,000 units:USE NORTHWINDSELECT ProductID, SUM(Quantity)FROM [Order Details]GROUP BY ProductIDHAVING SUM(Quantity) > 1000If you want to include regular restrictions, you can use the WHERE clause as well.To see prod-uct names instead of IDs, add a slightly longer statement that includes the Products table and mapsthem to the ProductIDs in the Order Details table with a WHERE clause:USE NORTHWINDSELECT Products.ProductName,[Order Details].ProductID,SUM(Quantity) AS [Items Sold]FROM Products, [Order Details]WHERE [Order Details].ProductID = Products.ProductIDGROUP BY [Order Details].ProductID, Products.ProductNameHAVING SUM(Quantity) > 1000ORDER BY Products.ProductNameIN and NOT IN KeywordsThe IN and NOT IN keywords are used in a WHERE clause to specify a list of values that a columnmust match (or not match).They are more of a shorthand notation for multiple OR operators
[ Pobierz całość w formacie PDF ]