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
- 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