Skip to main content
Aggregate functions perform the function operation on multiple rows and returns a single value, even when the input contains zero rows. Aggregate functions can be used in the SELECT list, or in a subquery (see examples below). When used with non-aggregate expression(s), a GROUP BY operation is needed on the non-aggregated expression(s). When using GROUP BY, each aggregate result is computed over the rows matching that GROUP BY column value (see examples below). The following table shows the available aggregate functions: Note that all the functions other than COUNT return NULL when no rows are selected (COUNT returns zero). Notes:
  1. AVG(): When the column data type is integral or DECIMAL, the return value is DECIMAL. Otherwise, the return value is DOUBLE.
  2. When the column data type is an approximate numeric data type (DOUBLE, FLOAT, REAL), the return value is DOUBLE.
  3. COUNT(input_column): When using the COUNT() function over a specific column/expression, only the non-NULL values are counted. E.g, if the table has 4 rows and the column values are (1,2,NULL,4), the COUNT() of this column returns 3.
  4. Note that in the case of COUNT(*), all rows are counted, including rows with only NULLs in their column values.
  5. COUNT (DISTINCT): The DISTINCT can come only once in a given select statement.
  6. SUM(): The returned value depends on the input data type: 6.1. When integral_type or DECIMAL, the returned value is DECIMAL 6.2. When DOUBLE, the returned value is DOUBLE
  7. STDDEV(): The return value depends on the column data type: 7.1. When integral_type or DECIMAL the return value is DECIMAL 7.2. When DOUBLE, the return value is DOUBLE.