Specifying Records To Be Included in a Group
You might want to limit which records will be included in a group.
To do so, use WHERE. The following example transfers
the average and lowest prices of each part for those records for which
the delivery time (DELIVTIME) is less than 30 days.
Library/File (Member) | SUPPLIERS |
GROUP BY | PARTNO |
SELECT | PARTNO, AVG(PRICE), MIN(PRICE) |
WHERE | DELIVTIME < 30 |
The result is as follows:
Field: PARTNO AVG(PRICE) MIN(PRICE)
------ ---------- ----------
Record 1: 221 .25 .20
2: 231 .10 .10
3: 222 .23 .20
4: 232 .10 .10
5: 241 .07 .05
6: 209 18.75 18.00
7: 285 21.00 21.00
8: 295 8.50 8.50
9: 207 29.00 29.00
Note that the conditions specified in WHERE are checked first, then the records that satisfy those conditions are included in the group.