Example 3: Summarizing records with GROUP BY

What is the least expensive mineral water product? To answer this question, you must obtain the product costs from multiple suppliers for all mineral water products in each vendor's product line, then average the costs, group the data by vendor, and sort the data by cost.

With SQL, you can use summary records to obtain the answer by running a single query. The PRODUCT file includes a ProdCat (product category) field in which all mineral water products are identified by the value 100570100. The ProdName field contains the vendor name.

To transfer a summary record for each product name:

Specify the GROCERY/PRODUCT, GROCERY/SUPPROD, and GROCERY/SUPPLIER files in the Lib/File(Member) field on the main file transfer screen, then open the From AS/400 - Options dialog box.
To transfer a summary record of the average cost and the product name field, type this string in the Select field on the Overview page:
AVG(T2.PRODCOST), T1.PRODNAME
To join records by product ID and supplier ID, type the following string in the Join By field:
T1.PRODID = T2.PRODID AND T2.SUPID = T3.SUPID
To search records that are mineral water products, type the following string in the Where field:
T1.PRODCAT =100570100
To perform summary calculations on records of the same product name, type the following string in the Group By field:
T1.PRODNAME
To sort the results from lowest to highest product cost, type the following string in the Order By box:
AVG(T2.PRODCOST)

Click Apply to update the query fields.

The Query box at the top of the Overview page displays the complete query statement:

SELECT AVG(T2.PRODCOST), T1.PRODNAME
FROM grocery/product, grocery/supprod, grocery/supplier
WHERE (T1.PRODID=T2.PRODID AND T2.SUPID=T3.SUPID) 
AND (T1.PRODCAT=’100570100’)
GROUP BY T1.PRODNAME
ORDER BY AVG(T2.PRODCOST)

The resulting records are:

	ProdCost
Whisper Rain Mineral Water	3.00
Maple Falls Mineral Water	3.17
Mt. Baker Sparkling Water	3.35
Panda Mineral Water	3.62
Panda Sparkling Lemon Essence	3.81
Panda Sparkling Lime Essence	3.81