Aggregate Functions
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 Type | Description |
|---|---|---|
| count(*), count(expr), count(DISTINCT expr[, expr...]) | BIGINT | Return the total number of retrieved records. |
| sum(col), sum(DISTINCT col) | DOUBLE | Return the sum of the values in a column. |
| avg(col), avg(DISTINCT col) | DOUBLE | Return the average of the values in a column. |
| min(col) | DOUBLE | Return the minimum value of a column. |
| max(col) | DOUBLE | Return the maximum value of a column. |
| variance(col), var_pop(col) | DOUBLE | Return the variance of a numeric column. |
| var_samp(col) | DOUBLE | Return the sample variance of a numeric column. |
| stddev_pop(col) | DOUBLE | Return the deviation of a numeric column. |
| stddev_samp(col) | DOUBLE | Return the sample deviation of a numeric column. |
| covar_pop(col1, col2) | DOUBLE | Return the covariance of a pair of numeric columns. |
| covar_samp(col1, col2) | DOUBLE | Return the sample covariance of a pair of numeric columns. |
| corr(col1, col2) | DOUBLE | Return the coefficient of correlation of a pair of numeric columns. |
| percentile(BIGINT col, p) | DOUBLE | Return the exact pth percentile of a column. p must be between 0 and 1. Otherwise, this function returns null. This function does not work with floating point types. |
| percentile_approx(DOUBLE col, p [, B]) | DOUBLE | Return an approximate pth percentile of a numeric column (including floating point types) in a group. p must be between 0 and 1. B controls approximation accuracy. Higher values of B mean better approximations, and the default value is 10,000. When the number of distinct values in the numeric column is smaller than B, an exact percentile value is returned. |
Functions such as var_pop, stddev_pop, var_samp, stddev_samp, covar_pop, covar_samp, corr, and percentile_approx, do not support non-numeral data types, such as TimeStamp.
Last Article: String Functions
Next Article: Window Functions
Did this article solve your problem?
Thank you for your score！Your feedback would help us improve the website.