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.