DB2 ‘Aggregate’ function tricky questions

What is an aggregate function-An aggregate function receives a set of values for each argument (such as the values of a column) and returns a single-value result for the set of input values.

There are many aggregate functions in DB2…

Those are…

SUM
Returns the total value.
MIN
Returns the minimum value.
AVG
Returns the average value.
MAX
Returns the maximum value.
COUNT
Returns the number of selected rows.
COUNT_BIG
Returns the number of rows or values in a set of rows or values. The result can be greater than the maximum value of an integer.
XMLAGG
Returns a concatenation of XML elements from a collection of XML elements.

How to find a function is aggregate function

When a function receives single value, that function is called aggregate function.

Note: The following information applies to all aggregate functions, except for the COUNT() and COUNT_BIG(), variations of the COUNT and COUNT_BIG functions, and the XMLAGG function.

Syntax for aggregate function

``````SELECT SUM(SALARY) AS SUMSAL,
MIN(SALARY) AS MINSAL,
AVG(SALARY) AS AVGSAL,
MAX(SALARY) AS MAXSAL,
COUNT(*) AS CNTSAL
FROM EMP1
WHERE DEPT = 'A00';``````

The result table looks like this:

``````   SUMSAL       MINSAL             AVGSAL       MAXSAL     CNTSAL
=========     ========     ==============     ========     ======
128500.00     29250.00     42833.33333333     52750.00          3 ``````

How aggregate function works explained in a flow chart.

The argument of an aggregate function is a set of values derived from an expression. The expression must not include another aggregate function or a scalar fullselect. The scope of the set is a group or an intermediate result table, as explained in the information on the GROUP BY clause.

If a GROUP BY clause is specified in a query and the intermediate result from the FROM, WHERE, GROUP BY, and HAVING clauses is the empty set, then the aggregate functions are not applied and the result of the query is the empty set.

For example, the result of the following SELECT statement is the number of distinct values of JOB for employees in department D11:

SELECT COUNT(DISTINCT JOB)
FROM DSN8A10.EMP
WHERE WORKDEPT = ‘D11’;

The keyword DISTINCT is not an argument of the function but rather a specification of an operation that is performed before the function is applied. If DISTINCT is specified, redundant duplicate values are eliminated. If ALL is implicitly or explicitly specified, redundant duplicate values are not eliminated.

DISTINCT must not be specified preceding an XML value.

For compatibility with other SQL implementations, UNIQUE can be specified as a synonym for DISTINCT in aggregate functions.

When interpreting the DISTINCT clause for decimal floating-point values that are numerically equal, the number of significant digits in the value is not considered. For example, the decimal floating-point number 123.00 is not distinct from the decimal floating-point number 123. The representation of the number returned from the query will be any one of the representations encountered.

An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group.

If the expression includes more than one column name, each column name must be a correlated reference to the same group.

The result of the COUNT and COUNT_BIG functions cannot be the null value. As specified in the description of AVG, MAX, MIN, STDDEV, SUM, and VARIANCE, the result is the null value when the function is applied to an empty set. However, the result is also the null value when the function is specified in an outer select list, the argument is given by an arithmetic expression, and any evaluation of the expression causes an arithmetic exception (such as division by zero).

•  If the argument values of an aggregate function are strings from a column with a field procedure, the function is applied to the encoded form of the values and the result of the function inherits the field procedure.

Ref:IBM 