Database Reference
In-Depth Information
GROUPING
Recall from Chapter 2 that grouping means creating groups of records that share some common characteris-
tic. When grouping customers by sales rep number, for example, the customers of sales rep 20 would form
one group, the customers of sales rep 35 would form a second group, and the customers of sales rep 65 would
form a third group.
In Example 22, you need to group customers by rep number to perform the necessary calculations.
96
EXAMPLE 22
For each sales rep, list the rep number, the number of customers assigned to the rep, and the average bal-
ance of the rep
'
s customers. Group the records by rep number and order the records by rep number.
This type of query requires grouping by rep number to make the correct calculations for each group. To
indicate grouping in SQL, you use the GROUP BY clause, as shown in Figure 3-43. It is important to note
that the GROUP BY clause does not mean that the query results will be sorted. To display the query results
in a particular order, you must use the ORDER BY clause. The query design in Figure 3-43 uses the ORDER
BY clause to sort the query results by rep number.
Groups
records by rep
number
Orders records by
rep number
FIGURE 3-43
SQL query to group and sort records
The query results appear in Figure 3-44.
Rep 20
Number of
customers of
rep 20
Average b
alance of
customers of rep 20
FIGURE 3-44
Query results
