Databases Reference
In-Depth Information
Consider the following query:
Find the average number of units of the different products that Salesper-
son 137 has sold (i.e., the average of the quantity values in the first three
records of the SALES table).
Using the AVG operator, you would write:
SELECT AVG(QUANTITY) FROM SALES WHERE SPNUM=137
The result would be:
AVG (QUANTITY)
443.67
To find the total number of units of all products that she has sold, you would
use the SUM operator and write:
SELECT SUM (QUANTITY) FROM SALES WHERE SPNUM=137
The result would be:
SUM (QUANTITY)
1331
Notice that when used with the SELECT statement, the aggregate function is
used as either part of the SELECT list, or as in these examples, the entire SELECT
list.
Now, let's take a look at an example that relies on grouping rows, once
again using the SALES table in Figure 6-5. At some point, you might want to
find the total number of units of all products that each salesperson has sold.
That is, you want to group together the rows of the SALES table that belong
to each salesperson and calculate a value—the sum of the Quantity attribute
values in this case—for each such group. Here is the way such a query might
be stated: “Find the total number of units of all products sold by each sales-
person.” The SQL statement, using the GROUP BY clause, would look like
this:
SELECT SPNUM, SUM (QUANTITY) FROM SALES GROUP BY SPNUM
The result would be:
SPNUM SUM(QUANTITY)
137 1331
186 9307
204 1543
361 9577
Notice that GROUP BY SPNUM specifies that the rows of the table are to be
grouped together based on having the same value in their SPNUM attribute. All
