Aggregate Function
An aggregate function performs a calculation operation on a set of input values and returns a value. For example, the COUNT function counts the number of rows retrieved by an SQL statement. Table 1 lists aggregate functions.
Function |
Return Data Type |
Description |
---|---|---|
COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*) |
BIGINT |
Returns the number of input rows for which the expression is not NULL. Use DISTINCT for one unique instance of each value. |
COUNT(*) COUNT(1) |
BIGINT |
Returns the number of input rows. |
AVG([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the average (arithmetic mean) of expression across all input rows. Use DISTINCT for one unique instance of each value. |
SUM([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the sum of expression across all input rows. Use DISTINCT for one unique instance of each value. |
MAX([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the maximum value of expression across all input rows. |
MIN([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the minimum value of expression across all input rows. |
STDDEV_POP([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the population standard deviation of expression across all input rows. |
STDDEV_SAMP([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the sample standard deviation of expression across all input rows. |
VAR_POP([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the population variance (square of the population standard deviation) of expression across all input rows. |
VAR_SAMP([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the sample variance (square of the sample standard deviation) of expression across all input rows. |
COLLECT([ ALL | DISTINCT ] expression) |
MULTISET |
Returns a multiset of expression across all input rows. |
VARIANCE([ ALL | DISTINCT ] expression) |
DOUBLE |
Returns the sample variance (square of the sample standard deviation) of expression across all input rows. |
FIRST_VALUE(expression) |
Actual type |
Returns the first value in an ordered set of values. |
LAST_VALUE(expression) |
Actual type |
Returns the last value in an ordered set of values. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.