Specify a value computed from a group of rows.

 

Syntax

 

<aggregate function> ::=

 

|

 

COUNT ( * )

<general set function>

 

<general set function> ::=

 

 

 

<set function type> ( [ DISTINCT | ALL ] <value expression> )

 

<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