Databases Reference
In-Depth Information
the GROUP BY clause. For instance, suppose you wanted to find the total num-
ber 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 salesperson.
The SQL statement, using the GROUP BY clause, would look like this:
SELECT SPNUM, SUM(QUANTITY) AS [SUM]
FROM SALES GROUP BY SPNUM
This query produces the following result:
SPNUM SUM
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 of the rows for Salesperson Number 137 will form one group, all of the rows
for Salesperson Number 186 will form another group, and so on. The Quantity
attribute values in each group will then be summed—SUM(QUANTITY)—
and the results returned to the user. But it is not enough to provide a list of
sums:
1331
9307
1543
9577
These are the sums of the quantities for each salesperson, but because they
don't identify which salesperson goes with which sum, they are meaningless!
That's why the SELECT clause includes both SPNUM and SUM(QUANTITY).
Including the attribute(s) specified in the GROUP BY clause in the SELECT
clause allows you to properly identify the sums calculated for each group. A SQL
statement with a GROUP BY clause may also include a WHERE clause. Thus the
query
Find the total number of units of all products sold by each salesperson
whose salesperson number is at least 150.
would look like:
SELECT SPNUM, SUM(QUANTITY) AS [SUM]
FROM SALES WHERE SPNUM> =150 GROUP BY SPNUM
