2.14 Set functions

Organization of this section
(1) Function
(2) Format
(3) Rules for when a subquery is not used
(4) Rules for when a subquery is used
(5) Common rules
(6) Usage examples

(1) Function

Specifying a set function in SQL allows you to calculate the average, sum, maximum value, minimum value, and the number of rows, as well as to concatenate XML type values.

For details about the XMLAGG set function, see 1.14.5(1) XMLAGG.

The following table lists the functions of the set functions.

Table 2-24 Functions of set functions

ItemSet function
AVGSUMMAXMINCOUNTCOUNT_FLOATXMLAGG
FunctionCalculates an average.Calculates a sum.Calculates a maximum value.Calculates a minimum value.Calculates the number of rows.Calculates the number of rows.Joins XML type values.
Treatment of null valueIgnoredIgnoredIgnoredIgnoredIgnored#1Ignored#1Ignored
Meaning of DISTINCT specificationAverage value obtained by removing rows that contain a duplicated value in specified columns.Sum obtained by removing rows that contain a duplicated value in specified columns.Has no meaning.Has no meaning.Number of rows after removing rows that contain an identical value in specified value expressions.Number of rows obtained by removing rows that contain a duplicated value in specified columns.Cannot be specified.
Function value for a set in which the target of application is either zero#3 or only the null valueNull valueNull valueNull valueNull value00Null value
Whether a repetition column can be specified in an argument#2Cannot be specified.Cannot be specified.Can be specified.Can be specified.Can be specified.Cannot be specified.Can be specified.

#1: A null value is ignored in most set functions, except for COUNT(*) and COUNT_FLOAT(*), where the total number of rows that satisfy the conditions is calculated regardless of null values.

#2: With the set functions MAX and MIN, a repetition column can be specified in an argument by coding a FLAT specification. If a repetition column is specified, the set functions MAX and MIN calculate either a maximum or a minimum from all the elements in the rows within the scope of the operation. This process excludes any columns in which the values of the entire column are the null value (a repetition column in which the number of elements is 0).

#3: If a GROUP BY or HAVING clause is specified, groups for which the number of rows is 0 are excluded from the calculation.

Table 2-25 Relationships between data types of columns and data types of function values

Data type of set function argumentAVGSUMMAX and MINCOUNTCOUNT_FLOATXMLAGG
INTEGERINTEGERINTEGERINTEGERINTEGERFLOAT--
SMALLINTINTEGERINTEGERSMALLINTINTEGERFLOAT--
DECIMAL(p,s)DECIMAL(max_prec#3, max_prec#3 - p + s)DECIMAL(max_prec#3, s)DECIMAL(p,s)INTEGERFLOAT--
FLOATFLOATFLOATFLOATINTEGERFLOAT--
SMALLFLTSMALLFLTSMALLFLTSMALLFLTINTEGERFLOAT--
INTERVAL YEAR TO DAY----INTERVAL YEAR TO DAYINTEGERFLOAT--
INTERVAL HOUR TO SECOND----INTERVAL HOUR TO SECONDINTEGERFLOAT--
CHAR(n)----CHAR(n)#1INTEGERFLOAT--
VARCHAR(n)----VARCHAR(n)#1INTEGERFLOAT--
NCHAR(n)----NCHAR(n)INTEGERFLOAT--
NVARCHAR(n)----NVARCHAR(n)INTEGERFLOAT--
MCHAR(n)----MCHAR(n)INTEGERFLOAT--
MVARCHAR(n)----MVARCHAR(n)INTEGERFLOAT--
DATE----DATEINTEGERFLOAT--
TIME----TIMEINTEGERFLOAT--
TIMESTAMP----TIMESTAMPINTEGERFLOAT--
BINARY(n)#2----BINARY(n)INTEGERFLOAT--
BLOB(n)------------
BOOLEAN------------
XML----------XML
Abstract data type(not including XML type)------------

--: Cannot be used.

#1
The character set of the result is the same as the character set of the value expression specified in the argument of the set function.
#2
n must be less than or equal to 32,000.
#3
max_prec is the maximum precision value of the DECIMAL type. The table below lists the values of max_prec. For details about the pd_sql_dec_op_maxprec operand, see the manual HiRDB Version 9 System Definition.

Table 2-26 Maximum precision value of the DECIMAL type

System common definition pd_sql_dec_op_maxprec operandPrecision p of set function argumentmax_prec value
29 or omittedp[Figure] 2929
p > 2938
38Any38

(2) Format

set-function::={COUNT(*)|COUNT FLOAT(*)|ALL set-function|DISTINCT set-function|XMLAGG-set-function}
ALL set-function::={AVG|SUM|MAX|MIN|COUNT|COUNT FLOAT}([ALL] {value-expression|FLAT-specification})
DISTINCT set-function::= {AVG|SUM|MAX|MIN|COUNT|COUNT FLOAT}
                       (DISTINCT {value-expression|FLAT-specification})
FLAT-specification:: = FLAT (column-specification)

For details about the format of the XMLAGG set function, see 1.14.5(1) XMLAGG.

(3) Rules for when a subquery is not used

  1. When GROUP BY, WHERE, or FROM clauses are specified, the groups that are obtained as a result of the last specified clause are used as the input to the set function.
    However, if the GROUP BY clause is not specified, the result of either the WHERE clause or the FROM clause becomes a group that does not have grouped columns (value expressions that are specified in the GROUP BY clause).
    The results of an operation by a set function are obtained for each group.
  2. A set function can be specified only in the SELECT or HAVING clause.
  3. When the GROUP BY clause, the HAVING clause, or a set function is specified, one of the following column specifications must be used in the SELECT or HAVING clause:
    • Grouped columns (value expressions specified in the GROUP BY clause)
    • Specified in the argument of a set function
  4. The SELECT DISTINCT specification and DISTINCT set-function are mutually exclusive.

(4) Rules for when a subquery is used

  1. When a set function is specified in a subquery, the following can be used as input to the set function:
    • A set function can be specified for each query specification (including specifications inside the parentheses in a subquery). The sets that can be used as input to the set function are determined for each query specification.
    • The set function that is the input object of COUNT(*) or COUNT_FLOAT(*) is determined by the query specification that directly includes COUNT(*) or COUNT_FLOAT(*). Other sets that can be used as input to the set function are determined on the basis of the query specification that uses the FROM clause to specify the table that is referenced in the argument.
    • If a GROUP BY clause, a WHERE clause, or a FROM clause is specified in a query specification, the group that is obtained as the result of the last specified clause is used as the input to the set function.
    If a GROUP BY clause is not specified, the group that results from the WHERE or FROM clause does not have a grouping column (value expressions specified in a GROUP BY clause). The results of operation by a set function are obtained on a group-by-group basis.
  2. A set function for query specification Q can be specified only in the SELECT or HAVING clause in query specification Q. A set function can also be specified in the ON search condition in a FROM clause, the WHERE clause, or HAVING clause in a subquery of the HAVING clause by referencing a column in the table for query specification Q as an argument (outer referencing).
  3. When a GROUP BY clause or a HAVING clause is specified in a query specification, or when a set function is specified in a SELECT clause, any column specification in a SELECT or HAVING clause in the query specification must meet the following conditions:
    Columns specified in a SELECT clause
    • The column specification must reference the table in the FROM clause in the query specification.
    • The column specification either must be a grouping column or must be specified in an argument in the set function.
    Columns specified in a HAVING clause
    • The column specification references either the table in the FROM clause in the query specification or a table in the FROM clause in an outer query specification (outer referencing).
    • The column specification references a table in the FROM clause in the query specification, is a grouping column, or is specified in an argument in the set function.
  4. If SELECT DISTINCT is specified in a query specification, a set function specifying DISTINCT for the query specification cannot be specified. A set function specifying the DISTINCT option is called a DISTINCT set function.
  5. In a given query, an argument in a set function cannot specify an operation that includes an outer-referencing column.

(5) Common rules

  1. A set function cannot be specified in any of the following clauses and statements: SET clause, IF statement, WHILE statement, SET statement, RETURN statement, WRITE LINE statement, ADD clause, GROUP BY clause.
  2. Embedded variables and ? parameters cannot be specified in the argument of a set function.
    However, if an XMLQUERY function is specified in the value expression of an XMLAGG set function, embedded variables and the ? parameter can be specified in the value expression of an XML query variable in that XMLQUERY function.
  3. Value expressions containing column specifications must be specified in the argument of a set function.
  4. A set function or the window function cannot be specified in the argument of a set function.
  5. The collating sequence for character string data is based on the character encoding used by the character set of the value expression specified in the argument of the set function.
  6. The collating sequence of national character string data is based on the national character code being used (Shift JIS code, EUC Japanese kanji code, or EUC Chinese kanji code).
  7. The collating sequence of mixed character string data is based on the ASCII encoding and national character encoding being used (Shift JIS code, EUC Japanese kanji code, EUC Chinese kanji code, Chinese kanji code (GB18030), or Unicode (UTF-8)).
  8. In averages (AVG), the digits following the significant digits are rounded off.
  9. An error results if overflow occurs during a computation, unless the overflow error suppression feature is set. The overflow error suppression feature is applicable to the following set functions:
    • AVG
    • SUM
    • COUNT
    • COUNT FLOAT
    For details about the operational results produced when the overflow error suppression feature is set, see 2.18 Operational results with overflow error suppression specified.
  10. If the value of COUNT_FLOAT is 253 (16 digits) or greater, the value is rounded off.
  11. Component specifications cannot be specified in an argument of a set function.
  12. The following rules apply to the specification of repetition columns in an argument in a set function:
    • Repetition columns can be specified only in the MAX or MIN set function for which a FLAT specification is coded.
    • Only unsubscripted repetition columns can be specified in a column specification that is coded in a FLAT specification.
    • Set functions for which a FLAT specification is coded cannot be specified in a query specification in which a value expression other than a column specification is specified in the GROUP BY clause.
  13. Normally, only one DISTINCT set function can be specified per query specification.
    However, in the following cases, multiple DISTINCT set functions can be specified:
    • When different value expressions can be specified in the MAX and MIN set functions
    • Values described using the same format can be specified in set functions AVG, SUM, COUNT, and COUNT_FLOAT.
  14. A subquery cannot be specified in a value expression that is specified as an argument of a set function.

(6) Usage examples

  1. Determine the average stock quantities (SQUANTITY) of all products in a stock table (STOCK):

    SELECT AVG(SQUANTITY) FROM STOCK

  2. Determine the sum of the stock quantities (SQUANTITY) of the rows whose product name (PNAME) is skirt in a stock table (STOCK):

    SELECT N'skirt',SUM(SQUANTITY)
       FROM STOCK
       WHERE PNAME=N'skirt'

  3. Determine the total sum of each product's quantity in stock (SQUANTITY) times its unit price (PRICE) from a stock table (STOCK):

    SELECT SUM(PRICE*SQUANTITY)
       FROM STOCK

  4. Determine the maximum, minimum, and total number of entries for the quantities in stock (SQUANTITY) in a stock table (STOCK):

    SELECT COUNT(*),MAX(SQUANTITY),MIN(SQUANTITY)
       FROM STOCK

  5. From the following table of competition results, determine the highest score (maximum value) and the lowest score (minimum value) for the event with an event code 0001; assume that the column of scores is a repetition column for which the number of elements is 3:

    [Figure]