[ Pobierz całość w formacie PDF ]
.Aggregate Functions and the GROUP BY ClauseAggregate functions enable you to obtain sums, averages, and so on, from the database.However, attimes you need to obtain sets of sums or sets of averages.For instance, you might need to calculate thetotal sales revenue for each month during the past year or to discover the average number of productspurchased each week for the past six months.These are the cases where you need to use the aggregatefunctions with the GROUP BY clause.To try the GROUP BY clause, you first need to select a set of records.Try the query shown in Listing8.6.Listing 8.6.Using the SELECT Statement to Retrieve All Products Purchased, the OrderNumber, and the Date1: SELECT neworders.ordernumber, neworders.orderdate,2: productspurchased.*3: FROM neworders, productspurchased4: WHERE neworders.ordernumber =5: productspurchased.ordernumberThe query shown in Listing 8.6 will return all the products that have been sold, the order number eachwas sold under, and the date of the order.The results are shown in Figure 8.1.Figure 8.1 : All products purchased, the order number, and the date. It would be interesting to see the total price of each order.This means that you need to add up thePrice and the ShippingAndHandling fields for every record on each order.You saw in Listing8.5 how to obtain the total of a single order.How do you find the total of every order? Listing 8.7shows you how.Listing 8.7.Using the SUM Function with the GROUP BY Clause to Calculate the Totals ofEach Order1: SELECT neworders.ordernumber,2: SUM(price + shippingandhandling)3: FROM neworders, productspurchased4: WHERE neworders.ordernumber = productspurchased.ordernumber5: GROUP BY neworders.ordernumberLine 1 of Listing 8.7 selects the order number (so you have the context for the totals).Line 2 selectsthe sum of the Price and ShippingAndHandling fields.Lines 3 and 4 specify the tables and thejoin.(Actually, a join with the NewOrders table isn't necessary in this query because all the fieldsyou need are in the ProductsPurchased table.Having the NewOrders table in this querydoesn't hurt anything, so just leave it in because the next query will require the OrderDate fieldfrom NewOrders.)Line 5 specifies that you want the sum of the Price and ShippingAndHandling fields groupedby OrderNumber.Figure 8.2 shows the output of this query.Figure 8.2 : The total price of each order.If you look at Figure 8.1 and add up the numbers yourself, you will see that the totals in Figure 8.2 arecorrect.With the small amount of data in this example, you could probably perform these calculationsinside the client application (or even in your head).However, imagine the amount of work required toprocess thousands of orders or tens of thousands of product sales.If you retrieved all those records anddid the calculations at the client, the application would bog down.The GROUP BY clause lets youperform all the work at the server and then return only the small resultset to the client.Is that cool, orwhat?It would be interesting to see the total sales volume per week.To do this, you must group by the orderdate.More specifically, you must group by the week of the order date.Even more specifically, youmust group by the year and the week, in case you have orders that span more than one year.The GROUP BY clause lets you group by multiple fields and multiple expressions.Listing 8.8 shows howto retrieve the total sales volume per week.Listing 8.8.Using the SUM Function with the GROUP BY Clause to Show the Total SalesVolume per Week1: SELECT DATEPART ('yyyy', neworders.orderdate) AS Year,2: DATEPART ('ww', neworders.orderdate) AS Week,3: SUM(price + shippingandhandling) AS Total4: FROM neworders, productspurchased5: WHERE neworders.ordernumber = productspurchased.ordernumber6: GROUP BY DATEPART ('yyyy', neworders.orderdate),7: DATEPART ('ww', neworders.orderdate)Line 1 of Listing 8.8 uses the DATEPART function to retrieve the year.The AS Year modifier on theend makes it so that the first field in the resultset has a name of Year.Specifying a name for a fieldthat contains an expression is handy because it gives you an easy way to access the field in ADO withthe GetCollect function.(You learned about the GetCollect function on Day 4, "Retrieving SQL DataThrough a C++ API," in Listing 4.12.)Line 2 uses the DATEPART function to retrieve the week of the year for the OrderDate.It namesthis field Week.Line 3 selects (and calculates) the sum of the Price andShippingAndHandling fields in the group.This field is named Total.Lines 4 and 5 areidentical to Listing 8.6.Lines 6 and 7 group the results of the SUM function by both the year and theweek of the order date.In Figure 8.3, you see the results of the query in Listing 8.8.There are two records, one for the 45thweek and one for the 47th week of the year.No other weeks of the year had any sales, so they don'tappear in the resultset.Figure 8.3 : The total sales volume per week.The code in Listing 8.8 works with Access/Jet.However, the code to break down the date (theDATEPART function) and modifiers to specify the field names for expressions will vary slightly inSQL Server and Oracle.Check your database server documentation for details.NOTE In a client/server application, the Access/Jet database engine will processthe GROUP BY query shown in Listing 8.8 at the client machine (ratherthan at the server machine).Running a similar query on a relationaldatabase server, such as Oracle or SQL Server, will cause all the records tobe processed at the server machine and only the small resultset to be sent tothe client.This is one of the fundamental differences between relationaldatabase servers and databases, such as Access/Jet, that use ISAM files.SQL Server provides the CUBE and ROLLUP operators as optional switches in the GROUP BY clause.These operators produce super-aggregate rows, where the rows generated by the GROUP BY clauseare aggregated.The CUBE and ROLLUP operators are typically used in data warehouse applications.Oracle also provides extensions to the GROUP BY clause that are useful in data warehouseapplications.See your database server documentation for more information on extensions to theGROUP BY clause.As you can see, aggregate functions enable relational database servers to process and summarize vastquantities of information and make it available to thin clients across thin network connections.Theability to place intelligence at every tier in an application, and to use each tier where its strengths are,enables you to build highly advanced client/server and multitier systems.SQL ViewsA view is a virtual table whose contents are defined by a query (a SELECT statement).A view looks like a normal table.However, a view doesn't actually exist as a table in the database.Instead, the view is materialized whenever it is opened.A view can consist of fields and records fromseveral tables.A view's base query is the SELECT statement that defines the view.A view's base tables are the tables from which the view gets its data. The SELECT statement that defines the view (its base query) is stored in the database.The viewdoesn't store any data.In other words, no records are stored in the view.The records that appear in aview are actually stored in the view's base tables.Every time a view is opened, the database reads therecords into the view from its base tables by executing the view's base query.Views enable users to work with the particular data that interests them.For instance, in the sampleapplication, the company manager might want to be able to see the weekly sales totals on demand.Listing 8.9 shows the code to create a view that the manager could use to find that information.Listing 8.9
[ Pobierz całość w formacie PDF ]