skip to main content
Functions : Scalar functions : Aggregate functions
 

Aggregate functions

Aggregate functions return a single row based on groups of rows, rather than on single rows. The following table describes the supported aggregate functions.
 
Table 14: Aggregate functions 
Function
Description
AVG([ALL | DISTINCT] expression)
Returns the average of the values in a group. Null values are ignored.
COUNT({[ALL | DISTINCT] expression] | *})
Returns the number of items in a group.
COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of non-null values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, non-null values.
MAX([ALL | DISTINCT] expression)
Returns the maximum value in the expression.
MIN([ALL | DISTINCT] expression)
Returns the minimum value in the expression.
STDDEV([ALL | DISTINCT] expression)
STDDEV_SAMP([ALL | DISTINCT] expression)
STDEV([ALL | DISTINCT] expression)
Returns the Sample Standard Deviation of the values in a group. Null values are ignored.
STDDEVP([ALL | DISTINCT] expression)
STDDEV_POP([ALL | DISTINCT] expression)
STDEVP([ALL | DISTINCT] expression)
Returns the Population Standard Deviation of the values in a group. Null values are ignored.
SUM([ALL | DISTINCT] expression)
Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
VAR([ALL | DISTINCT] expression)
VAR_SAMP([ALL | DISTINCT] expression)
Returns the Sample Variance of the values in a group. Null values are ignored.
VARP([ALL | DISTINCT] expression)
VAR_POP([ALL | DISTINCT] expression)
Returns the Population Variance of the values in a group. Null values are ignored.
Aggregate functions can appear in SELECT lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, OpenAccess SDK divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the SELECT list must be expressions from the GROUP BY clause, expressions containing aggregate functions, or constants. OpenAccess SDK applies the aggregate functions in the SELECT list to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, OpenAccess SDK applies aggregate functions in the SELECT list to all the rows in the queried table or view.
Many aggregate functions accept these options:
• DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
• ALL causes an aggregate function to consider all values, including all duplicates.
For example:
SELECT max(sal), MIN(sal), AVG(sal) FROM emp;
SELECT deptno, MAX(sal), SUM(sal) FROM emp GROUP BY deptno;
SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno;
SELECT deptno, VAR_SAMP(sal), STDDEV_SAMP(sal) FROM emp GROUP BY deptno;
SELECT deptno, VAR_POP(sal), STDDEV_POP(sal) FROM emp GROUP BY deptno;
Note: Aggregate functions in denominator are not supported.
Note: Return value of statistical functions on a single value set is 0 (zero).