Filter
Top Products
Fetching data from a grouped table
A query specification is used to derive a table with which data is to be manipulated. If a query specification is used to
derive a table from a grouped table, the table is made up of a row for each group. In a query specification for which a
GROUP BY clause is specified, a grouped column, a grouped function, or a set function can be specified in the select
column list. If a set function is specified, the total, average, maximum, and minimum values, and the row count for the
column can be obtained for each group. If a column other than a grouped column is specified by other than a set
function, an error occurs. For example, suppose STOCKQTY is specified in the select column list in the query
specification in Example 1. In this case, whether the value for STOCKQTY for PRODUCT TELEVISION is "85", "90",
or "0" cannot be determined. However, a total value and an average value for these values can be determined, so a
set function can be specified. Also, in Figure: Example of a table derived from GROUP BY clause, the values of
WHCODE cannot be directly specified in the select column list despite the values being unique in each group. This is
the case because a value that is not unique may be produced at any time due to variations in the data. If columns
PRODUCT and WHCODE are specified as grouped columns for the data in Figure: Example of a table derived from
GROUP BY clause, the values of WHCODE values can be fetched. A literal or host variable can be specified in the
select column list
Example 2:
In this example, the totals for PRODUCT and STOCKQTY are obtained from the grouped table in
Example 1.
73