Queries That Group and Summarize Data

Frequently, you'll need to create queries that answer questions such as: How many orders came in yesterday? In this case, you don't care who ordered items; you only want to know how many orders came in. You can find out by using group queries and aggregate functions.

Aggregate queries summarize data according to one or more fields in common. For example, if you wanted to see how many orders have been placed by each customer, you'd perform a query on tblOrder grouping on the CustomerID field. The following is an example of such a query:

 SELECT CustomerID, COUNT(CustomerID) AS TotalOrders  FROM tblOrder GROUP BY CustomerID 

A similar result set is produced by this query.

CustomerID	TotalOrders
1	2
2	1
4	3
5	1
7	1
8	2

Note the use of the AS clause in the SQL expression. This clause is used to give the column containing the result of the aggregate function a name because it's calculated rather than stored in the database.

To display customer names instead of IDs, simply join data from tblCustomer, as in

 SELECT tblOrder.CustomerID, FirstName, LastName,      COUNT(dbo.tblOrder.CustomerID) AS TotalOrders FROM tblOrder INNER JOIN tblCustomer   ON tblOrder.CustomerID = tblCustomer.ID GROUP BY FirstName, LastName, CustomerID 

A similar result set is produced by this query.

CustomerID	FirstName	LastName	TotalOrders
1	John	Smith	2
2	Jill	Azalia	1
4	Daisy	Klein	3
5	Dave	Martin	1
7	Thurston	Ryan	1
8	Jane	Winters	2

In this case, the GROUP BY clause contains the CustomerID along with the FirstName and LastName fields joined from tblCustomer. When you use GROUP BY, you must include all the fields you're grouping on in this case, the customer ID and name fields are all involved in the grouping, so they must all appear in the GROUP BY clause. (Fortunately, if you forget to do that, the VS.NET development environment gently nudges you in the right direction.)

Using HAVING to Provide Criteria for Grouped Queries

We've already shown that a query criterion serves to limit the number of records retrieved by a query. In conventional SELECT queries, you use the WHERE clause to supply query criteria. However, in grouped queries, you use the HAVING clause instead. WHERE and HAVING are used in much the same way, although the criterion supplied by a HAVING clause applies to aggregated rows (that is, the product of the grouping), whereas the WHERE clause applies a criterion to individual rows. It may sound like a hair-splitting distinction by and large it is because nine times out of ten the two work nearly the same way. For example, you can use a criterion with grouping to return a report of sales activity for customer "Jane", as in the query

 SELECT tblOrder.CustomerID, FirstName, LastName,      COUNT(dbo.tblOrder.CustomerID) AS TotalOrders FROM tblOrder INNER JOIN tblCustomer   ON tblOrder.CustomerID = tblCustomer.ID GROUP BY FirstName, LastName, CustomerID HAVING FirstName = 'Jane' 

This query returns a single record, indicating that Jane Winters has placed two orders with the company. Now say that you want to display a list of frequent shoppers customers who have placed more than one order with your company. Because the aggregate number of orders is stored in the calculated field TotalOrders, you might think that you could use an expression such as HAVING TotalOrders> 1 to retrieve all your frequent customers. But unfortunately, this expression won't work because TotalOrders isn't a real field in the database it's a calculated field. Instead, you have to include the calculation in the HAVING clause, using a query such as

 SELECT tblOrder.CustomerID, FirstName, LastName,      COUNT(dbo.tblOrder.CustomerID) AS TotalOrders FROM tblOrder INNER JOIN tblCustomer   ON tblOrder.CustomerID = tblCustomer.ID GROUP BY FirstName, LastName, CustomerID HAVING (COUNT(tblOrder.CustomerID) > 1) 

The following result set is returned by this query.

CustomerID	FirstName	LastName	TotalOrders
1	John	Smith	2
4	Daisy	Klein	3
8	Jane	Winters	2

This query returns three rows, each representing a customer who has placed more than one order.

The SUM Function

You're not limited simply to counting records in aggregate functions. Using the SUM function, you can generate totals for all the records returned in numeric fields. For example, say that you want to create a query that lists the total number of items ordered by each customer. To do so, use

 SELECT OrderID, SUM(Quantity) AS TotalItems  FROM tblOrderItem GROUP BY OrderID 

This query produces the following result set.

OrderID	TotalItems
1	6
2	2
3	1
4	23
5	4
6	13
7	12
8	3
9	4
10	4

As with the previous examples that involve grouping, if you want to retrieve additional related information (such as the customer's first and last name), simply use a join. Remember, you must group on at least one field for an aggregate function to work.

Other SQL Aggregate Functions

Table 2.2 lists the aggregate functions available to you in SQL.

Table 2.2. SQL Aggregate Functions

Function	Result
AVG	The average of all values in the column
COUNT	The number of records returned
MAX	The maximum (or largest) value in a column
MIN	The minimum (or smallest) value in a column
STDEV	The standard deviation
SUM	The total of all values in the column
VAR	The statistical variance

The syntax of these aggregate functions is essentially the same as the syntax for COUNT and SUM, described in previous sections. For example, say that you want to get a sense of the average line-item quantity in each purchase, which is an aggregate calculation of the average number of items each customer purchases. To do so, use

 SELECT AVG(tblOrderItem.Quantity) AS AverageLineItemQuantity  FROM tblOrder INNER JOIN tblOrderItem ON tblOrder.ID = tblOrderItem.OrderID 

This query retrieves a single value, the number 2, indicating that when customers buy items from you, they buy them two at a time, on average.

You can combine calculations and aggregate functions in a variety of interesting ways. For example, say that you want a list of the total cost of all the orders in your database. You calculate the total cost of an order by multiplying the quantity (found in tblOrderItem) times the price (found in tblInventory) and then performing a SUM aggregate on that result. The query giving you the result you need is

 SELECT tblOrderItem.OrderID, SUM(Quantity * Price)    AS OrderTotal FROM tblInventory INNER JOIN    tblOrderItem ON tblItem.ID = tblOrderItem.ItemID GROUP BY OrderID 

This query produces the following result set.

OrderID	OrderTotal
1	15.64
2	7.98
3	5.99
4	99.17
5	13.96
6	49.07
7	55.88
8	13.97
9	9.16
10	14.76