|
Specify a value computed from a group of rows.
Syntax
<aggregate function> ::=
<general set function> ::=
<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:
|
SELECT studentID, AVG( grade )
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
|
|
