The following information applies to all column functions other than COUNT(*).
The argument of a column function is a set of values derived from one or more columns. The scope of the set is a group or an intermediate result table as explained in Chapter 4, "Queries".
If a GROUP BY clause is specified in a query and the intermediate result of the FROM, WHERE, GROUP BY, and HAVING clauses is the empty set, then the column functions are not applied, the result of the query is the empty set, the SQLCODE is set to +100, and the SQLSTATE is set to '02000'.
If a GROUP BY clause is not specified in a query and the intermediate result of the FROM, WHERE, and HAVING clauses is the empty set, then the column functions are applied to the empty set.
For example, the result of the following SELECT statement is the number of distinct values of JOB for employees in department D01:
SELECT COUNT(DISTINCT JOB) FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = 'D01'
The keyword DISTINCT is not considered an argument of the function, but rather a specification of an operation that is performed before the function is applied. If DISTINCT is specified, duplicate values are eliminated. If ALL is implicitly or explicitly specified, duplicate values are not eliminated.
The values of the argument are specified by an expression.
If a column-name is a correlated reference (which is allowed in a subquery of a HAVING clause), the expression must not include operators.
Following in alphabetical order is a definition of each of the column functions.
.-ALL------.
>>-AVG---(--+----------+--expression----)----------------------><
'-DISTINCT-'
|
The AVG function returns the average of a set of numbers.
The argument values must be any built-in numeric data type and their sum must be within the range of the data type of the result.
The data type of the result is the same as the data type of the argument values, except that:
The result can be null.
If the data type of the argument values is decimal or nonzero scale binary with precision p and scale s, the precision of the result is 31 and the scale is 31-p+s.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is used, duplicate values are eliminated.
If the function is applied to the empty set, the result is a null value. Otherwise, the result is the average value of the set.
The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.
If the type of the result is integer, the fractional part of the average is lost.
Using the PROJECT table, set the host variable AVERAGE (decimal(5,2)) to the average staffing level (PRSTAFF) of projects in department (DEPTNO) 'D11'.
SELECT AVG(PRSTAFF) INTO :AVERAGE FROM PROJECT WHERE DEPTNO = 'D11'
Results in AVERAGE being set to 4.25 (that is, 17/4) when using the sample table.
Using the PROJECT table, set the host variable ANY_CALC to the average of each unique staffing value (PRSTAFF) of projects in department (DEPTNO) 'D11'.
SELECT AVG(DISTINCT PRSTAFF) INTO :ANY_CALC FROM PROJECT WHERE DEPTNO = 'D11'
Results in ANY_CALC being set to 4.66 (that is, 14/3) when using the sample table.
.-ALL------.
>>-COUNT--(--+-+----------+---expression--+---)----------------><
| '-DISTINCT-' |
'-*--------------------------'
|
The COUNT function returns the number of rows or values in a set of rows or values.
The result of the function is a large integer and it must be within the range of large integers. The result cannot be null. If the table is a distributed table, then the result is DECIMAL(15,0). For more information about distributed tables, see the DB2 Multisystem book.
set of rows. The result is the number of rows in the set. A row that includes only null values is included in the count.
The argument of COUNT(expression) is a set of values. The function is applied to the set derived from the argument values by the elimination of null values. The result is the number of values in the set.
The argument of COUNT(DISTINCT expression) is a set of values. The argument values can be any values except character strings with a length attribute greater than 2000, graphic strings with a length attribute greater than 1000 DBCS or UCS-2 characters, LOBs, or DataLinks. The function is applied to the set of values derived from the argument values by the elimination of null values and duplicate values. The result is the number of values in the set.
If a sort sequence other than *HEX is in effect when the statement that contains the COUNT(DISTINCT expression) is executed and the arguments contain SBCS, UCS-2, or mixed data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the sort sequence.
Using the EMPLOYEE table, set the host variable FEMALE (int) to the number of rows where the value of the SEX column is 'F'.
SELECT COUNT(*)
INTO :FEMALE
FROM EMPLOYEE
WHERE SEX = 'F'
Results in FEMALE being set to 13 when using the sample table.
Using the EMPLOYEE table, set the host variable FEMALE_IN_DEPT (int) to the number of departments (WORKDEPT) that have at least one female as a member.
SELECT COUNT(DISTINCT WORKDEPT) INTO :FEMALE_IN_DEPT FROM EMPLOYEE WHERE SEX='F'
Results in FEMALE_IN_DEPT being set to 5 when using the sample table. (There is at least one female in departments A00, C01, D11, D21, and E11.)
Using the PROJECT table, set the host variable SUB_PROJECT_COUNT (int) to the number of projects that are sub-projects of a major project.
SELECT COUNT(MAJPROJ) INTO :SUB_PROJECT_COUNT FROM PROJECT
Results in SUB_PROJECT_COUNT being set to 14 when using the sample tables.
.-ALL------.
>>-COUNT_BIG--(--+-+----------+---expression--+---)------------><
| '-DISTINCT-' |
'-*--------------------------'
|
The COUNT_BIG function returns the number of rows or values in a set of rows or values. It is similar to COUNT except that the result can be greater than the maximum value of integer.
The result of the function is a decimal with precision 31 and scale 0. The result cannot be null.
The argument of COUNT_BIG(*) is a set of rows. The result is the number of rows in the set. A row that includes only null values is included in the count.
The argument of COUNT_BIG(expression) is a set of values. The function is applied to the set derived from the argument values by the elimination of null values. The result is the number of values in the set.
The argument of COUNT_BIG(DISTINCT expression) is a set of values. The argument values can be any values except character strings with a length attribute greater than 2000, graphic strings with a length attribute greater than 1000 DBCS or UCS-2 characters, LOBs, or DataLinks. The function is applied to the set of values derived from the argument values by the elimination of null values and duplicate values. The result is the number of values in the set.
If a sort sequence other than *HEX is in effect when the statement that contains the COUNT(DISTINCT expression) is executed and the arguments contain SBCS, UCS-2, or mixed data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the sort sequence.
Refer to COUNT examples and substitute COUNT_BIG for occurrences of COUNT. The results are the same except for the data type of the result.
To count on a specific column, a sourced function must specify the type of the column. In this example, the CREATE FUNCTION statement creates a sourced function that takes any column defined as CHAR, uses COUNT_BIG to perform the counting, and returns the result as a double precision floating-point number. The query shown counts the number of unique departments in the sample employee table.
CREATE FUNCTION RICK.COUNT(CHAR()) RETURNS DOUBLE SOURCE QSYS2.COUNT_BIG(CHAR()); SELECT COUNT(DISTINCT WORKDEPT FROM CORPDATA.EMPLOYEE;
Note that the input parameter for the sourced function is defined with empty parentheses to indicate that the input parameter inherits the attributes of the parameter of the source function.
.-ALL------.
>>-MAX---(--+----------+--expression----)----------------------><
'-DISTINCT-'
|
The MAX column function returns the maximum value in a set of values in a group.
The argument values can be any built-in data types except LOB and DataLink values.
The data type and length attribute of the result are the same as the data type and length attribute of the argument values. When the argument is a string, the result has the same CCSID as the argument. The result can be null.
If a sort sequence other than *HEX is in effect when the statement that contains the MAX function is executed and the arguments contain SBCS, UCS-2, or mixed data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the sort sequence.
The function is applied to the set of values derived from the argument values by the elimination of null values.
If the function is applied to the empty set, the result is a null value. Otherwise, the result is the maximum value in the set.
The specification of DISTINCT has no effect on the result and is not advised.
Using the EMPLOYEE table, set the host variable MAX_SALARY (decimal(7,2)) to the maximum monthly salary (SALARY / 12) value.
SELECT MAX(SALARY) /12 INTO:MAX_SALARY FROM EMPLOYEE
Results in MAX_SALARY being set to 4395.83 when using the sample table.
Using the PROJECT table, set the host variable LAST_PROJ (char(24)) to the project name (PROJNAME) that comes last in the collating sequence.
SELECT MAX(PROJNAME) INTO :LAST_PROJ FROM PROJECT
Results in LAST_PROJ being set to 'WELD LINE PLANNING' when using the sample table.
.-ALL------.
>>-MIN---(--+----------+--expression----)----------------------><
'-DISTINCT-'
|
The MIN column function returns the minimum value in a set of values in a group.
The argument values can be any built-in data types except LOB and DataLink values.
The data type and length attribute of the result are the same as the data type and length attribute of the argument values. When the argument is a string, the result has the same CCSID as the argument. The result can be null.
If a sort sequence other than *HEX is in effect when the statement that contains the MIN function is executed and the arguments contain SBCS, UCS-2, or mixed data, then the result is obtained by comparing weighted values for each value in the set.
The function is applied to the set of values derived from the argument values by the elimination of null values.
If the function is applied to the empty set, the result is a null value. Otherwise, the result is the minimum value in the set.
The specification of DISTINCT has no effect on the result and is not advised.
Using the EMPLOYEE table, set the host variable COMM_SPREAD (decimal(7,2)) to the difference between the maximum and minimum commission (COMM) for the members of department (WORKDEPT) 'D11'.
SELECT MAX(COMM) - MIN(COMM) INTO :COMM_SPREAD FROM EMPLOYEE WHERE WORKDEPT = 'D11'
Results in COMM_SPREAD being set to 1118 (that is, 2580 - 1462) when using the sample table.
Using the PROJECT table, set the host variable FIRST_FINISHED (char(10)) to the estimated ending date (PRENDATE) of the first project scheduled to be completed.
SELECT MIN(PRENDATE) INTO :FIRST_FINISHED FROM PROJECT
Results in FIRST_FINISHED being set to '1982-09-15' when using the sample table.
.-ALL------.
>>-STDDEV---(--+----------+--expression----)-------------------><
'-DISTINCT-'
|
The STDDEV function returns the biased standard deviation (/n) of a set of numbers. The formula used to calculate STDDEV is:
STDDEV = SQRT(VAR)
where SQRT(VAR) is the square root of the variance.
The argument values must be any built-in numeric data type and the sum must be within the range of the data type of the result.
The data type of the result is double-precision floating point. The result can be null.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.
If the function is applied to the empty set, the result is a null value. Otherwise, the result is the standard deviation of the values in the set.
The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.
Using the EMPLOYEE table, set the host variable DEV (FLOAT double precision) to the standard deviation of the salaries for those employees in department A00.
SELECT STDDEV(SALARY)
INTO :DEV
FROM EMPLOYEE
WHERE WORKDEPT = 'A00';
Results in DEV being set to approximately 9938.00 when using the sample table.
.-ALL------.
>>-SUM---(--+----------+--expression----)----------------------><
'-DISTINCT-'
|
The SUM function returns the sum of a set of numbers.
The argument values must be any built-in numeric data type and their sum must be within the range of the data type of the result.
The data type of the result is the same as the data type of the argument values except that the result is:
The result can be null.
If the data type of the argument values is decimal or nonzero scale binary, the precision of the result is 31 and the scale is the same as the scale of the argument values.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.
If the function is applied to the empty set, the result is a null value. Otherwise, the result is the sum of the values in the set.
The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.
Using the EMPLOYEE table, set the host variable JOB_BONUS (decimal(9,2)) to the total bonus (BONUS) paid to clerks (JOB='CLERK').
SELECT SUM(BONUS)
INTO :JOB_BONUS
FROM EMPLOYEE
WHERE JOB = 'CLERK'
Results in JOB_BONUS being set to 2800 when using the sample table.
.-ALL------.
>>-+-VARIANCE-+--(--+----------+--expression----)--------------><
'-VAR------' '-DISTINCT-'
|
The VAR and VARIANCE functions return the biased variance (/n) of a set of numbers. The formula used to calculate VAR is:
VAR = SUM(X**2)/COUNT(X) - (SUM(X)/COUNT(X))**2
The argument values must be any built-in numeric data type and the sum must be within the range of the data type of the result.
The data type of the result is double-precision floating point. The result can be null.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.
If the function is applied to the empty set, the result is a null value. Otherwise, the result is the variance of the values in the set.
The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.
Using the EMPLOYEE table, set the host variable VARNCE (FLOAT double precision) to the variance of the salaries for those employees in department A00.
SELECT VAR(SALARY) INTO :VARNCE FROM EMPLOYEE WHERE WORKDEPT = 'A00';
Results in VARNCE being set to approximately 98763888.88 when using the sample table.