Filter
Top Products
SELECT SUM (ORDERQTY) INTO :TOTALQTY FROM STOCKS. ORDER
Various set functions are available for determining values in columns. "SUM" is used to obtain the total value, "AVG" to
obtain the average value, "MAX" to obtain the maximum value, "MIN" to obtain the minimum value, and "COUNT" to
obtain the row count.
The reader has probably noticed that no WHERE clause was specified in the single row SELECT statement in
Example 1 to specify the row to be fetched. The single row SELECT statement can be used to fetch data from only
one row, so, in the previous examples in this manual, a condition specifying the row to be fetched was specified.
However, in Example 1, since the total for all rows of a table was to be fetched, no condition specifying rows was
required. Specify a WHERE clause to target only rows satisfying certain conditions for a total. When a WHERE clause
is specified, only those rows for which the search condition is true are totaled.
In Example 1, a set function was applied to the values in a column. Set functions can also be applied to the results of
operational expressions. The following is an example of applying a set function to the results of an operational
expression:
Example 2:
In this example, the totals of ORDERQTY and order price are obtained from the ORDER table for
CUSTOMER 72. Order price is obtained by multiplying PRICE by ORDERQTY.
SELECT SUM (ORDERQTY), SUM (PRICE * ORDERQTY)
INTO :TOTALQTY, :TOTALPRICE
FROM STOCKS. ORDER WHERE CUSTOMER = 72
[Figure: Example of applying a set function to the results of an operational expression]
17