Specify a value computed from a group of rows.

Syntax

<aggregate function> ::=

 | COUNT ( * )

<general set function> ::=

 ( [ DISTINCT | ALL ] )

<set function type> ::=

 | | | | | | | AVG MAX MIN SUM COUNT MED STD LIST

Usage

The aggregate functions, also referred to as set functions, perform operations that aggregate data from groups of rows.

Notes

 ▪ ALL is implicit if no set quantifier is not specified.

 ▪ If DISTINCT is specified, then all duplicate values are removed from the set.

 ▪ If the query specifies a GROUP BY clause, then the aggregate functions are applied to each group, else the entire table is treated as a single group.

 ▪ If a general set function is specified, other than the COUNT function, and the set is empty, then the result is null.

COUNT function

 ▪ If COUNT( * ) is specified, then the result is the cardinality of the set, else the COUNT function returns the number of non-null values in the set.

 ▪ The data type of the result is BIGINT.

MAX function

 ▪ The MAX function returns the highest non-null value in the set.

 ▪ The data type of the result is the same type as the argument.

MIN function

 ▪ The MIN function returns the lowest non-null value in the set.

 ▪ The data type of the result is the same type as the argument.

AVG function

 ▪ The AVG function calculates the average of non-null values in the set.

 ▪ The argument shall be a numeric type.

 ▪ The data type of the result is the same type as the argument.

SUM function

 ▪ The SUM function calculates the sum of non-null values in the set.

 ▪ The argument shall be a numeric type.

 ▪ If the argument is an integer type, then the data type of the result is BIGINT.

 ▪ If the argument is a BCD type, then the data type of the result is DECIMAL with maximum possible precision and the same scale as the argument.

 ▪ If the argument is an approximate numeric type, then the data type of the result is DOUBLE PRECISION or EXTENDED.

MED function

 ▪ The MED function calculates the median value of the non-null values in the set.

 ▪ The argument shall be a numeric type.

 ▪ The data type of the result is the same type as the argument.

STD function

 ▪ The STD function calculates the standard deviation of the non-null values in the set.

 ▪ The argument shall be a numeric type.

 ▪ The data type of the result is the same type as the argument.

LIST function

 ▪ LIST is a special NexusDB set function that compiles a comma-separated list of non-null values in the set.

 ▪ The argument shall be a character string type.

 ▪ The data type of the result is CHAR(4096).

Examples

 1) The following example calculates the number of rows in the students table:

 SELECT COUNT( * ) FROM students

 2) The following example returns the number of female students:

 SELECT COUNT( studentName )
 FROM students
 WHERE gender = 'F'

 3) The following example calculates the average grade for each student:

 FROM enrolls
 GROUP BY studentID

 4) The following example selects the highest and lowest salaries of teachers:

 SELECT MAX( salary ), MIN( salary )
 FROM teachers

 5) The following example calculates the total cost of each order made:

 SELECT orderID, SUM( extPrice )
 FROM order_details
 GROUP BY orderID

 6) The following example retrieves a list of student names grouped by gender:

 SELECT gender, LIST( studentName )
 FROM students
 GROUP BY gender

Conformance

 SQL:2003 standard   NexusDB extensions -   - - - Core SQL   MED function STD function LIST function