Show TOC

Set FunctionsLocate this document in the navigation structure

Set functions are aggregate functions (they return one value for groups of rows and therefore imply grouping of tables).

Syntax 

<set function> ::= COUNT '(' '*' ')' 
                          | COUNT '(' DISTINCT <value expression> ')'
                          | <general set function>.

<general set function> ::= 
                   ( SUM | AVG | MIN | MAX ) 
                  '(' ( DISTINCT | ALL )? <value expression> ')'.

The return type of set functions follow these rules:

  • COUNT: The return type is exact numeric with scale 0.

  • AVG: The return type is approximate numeric.

  • MIN, MAX, SUM: The return type is the type of the argument.

Examples 

SELECT AVG(salary) FROM employees

Average. This query determines the average salary of all employees.

SELECT COUNT(*) FROM employees

COUNT(*). This query determines the number of employees.

SELECT COUNT(DISTINCT employee_name) FROM employees

Average. This query determines the number of different names of the employees.

More Information

Value expressions