1.13.4 Example 4: Determine the quantity purchased for each product code (narrow down retrieval by specifying a HAVING clause)

Determine the sum and average of the quantities purchased on or after September 3, 2011 for each product code (PUR-CODE) in the sales history table (SALESLIST).

In this case, we retrieve only those product codes where the quantities purchased is 20 or fewer.

Table to search

[Figure]

Specification example

SELECT "PUR-CODE",SUM("PUR-NUM"),AVG("PUR-NUM")
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-03'
        GROUP BY "PUR-CODE"
        HAVING SUM("PUR-NUM")<=20

Retrieval results

[Figure]

To Page Top