Specifying Summary Records To Be Transferred

In some cases, you might want to transfer only summary records that satisfy certain conditions. The use of HAVING enables the selection of which summary records are to be transferred. WHERE is applied to certain records in a group, while HAVING is applied only to summary records.

The following example transfers the highest and lowest prices for each part. However, the summary records to be transferred are only those for which the highest price exceeds 10.00.
Library/File (Member) SUPPLIERS
GROUP BY PARTNO
SELECT PARTNO, MAX(PRICE), MIN(PRICE)
HAVING MAX(PRICE) > 10.00
The following table shows the result of removing unnecessary summary records by using HAVING 
     Field: PARTNO   MAX(PRICE)   MIN(PRICE)
            ------   ----------   ----------
  Record 1:    209        19.50        18.00
         2:    285        21.00        21.00
         3:    207        29.00        29.00

One summary record for an entire file can be transferred. To do this, specify only the summary function in SELECT and nothing in GROUP BY. As a result, an entire file can be recognized as one group, while one summary record can be transferred for the group.

You can concurrently use the concept of summarizing groups and that of joining records from several files. To obtain the desired results, do as follows:
  1. Specify a file in FROM, and specify the join conditions to join the records in JOIN BY.
  2. Specify the conditions in WHERE to remove unnecessary records.
  3. Specify the fields used for grouping the remaining records in GROUP BY.
  4. Specify the function in SELECT, then create summary records.
  5. Specify the conditions in HAVING to remove unnecessary records.
  6. Specify the items for grouping the final summary records in ORDER BY.