Help Center/ Data Lake Insight/ SQL Syntax Reference (To Be Offline)/ Historical Versions (Unavailable Soon)/ Flink SQL Syntax (This Syntax Will Not Evolve. Use FlinkOpenSource SQL Instead.)/ Built-In Functions/ Aggregate Functions
Updated on 2024-02-07 GMT+08:00
View PDF
Share

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.

Sample data: Table T1 
|score|
|81   |
|100  |
|60   |
|95   |
|86   |

Common Aggregate Functions

Table 1 Common aggregate functions

Function

Return Data Type

Description

COUNT(*)

BIGINT

Return count of tuples.

COUNT([ ALL ] expression...

BIGINT

Returns the number of input rows for which the expression is not NULL. Use DISTINCT for a unique instance of each value.

AVG(numeric)

DOUBLE

Return average (arithmetic mean) of all input values.

SUM(numeric)

DOUBLE

Return the sum of all input numerical values.

MAX(value)

DOUBLE

Return the maximum value of all input values.

MIN(value)

DOUBLE

Return the minimum value of all input values.

STDDEV_POP(value)

DOUBLE

Return the population standard deviation of all numeric fields of all input values.

STDDEV_SAMP(value)

DOUBLE

Return the sample standard deviation of all numeric fields of all input values.

VAR_POP(value)

DOUBLE

Return the population variance (square of population standard deviation) of numeral fields of all input values.

VAR_SAMP(value)

DOUBLE

Return the sample variance (square of the sample standard deviation) of numeric fields of all input values.

Example

  • COUNT(*)
    • Test statement 
      SELECT COUNT(score) FROM T1;
    • Test data and results
      Table 2 T1

      Test Data (score)

      Test Result

      81

      5

      100

      60

      95

      86
  • COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*)
    • Test statement 
      SELECT COUNT(DISTINCT content ) FROM T1;
    • Test data and results
      Table 3 T1

      content (STRING)

      Test Result

      "hello1 "

      2

      "hello2 "

      "hello2"

      null

      86
  • AVG(numeric)
    • Test statement 
      SELECT AVG(score) FROM T1;
    • Test data and results
      Table 4 T1

      Test Data (score)

      Test Result

      81

      84.0

      100

      60

      95

      86
  • SUM(numeric)
    • Test statement 
      SELECT SUM(score) FROM T1;
    • Test data and results
      Table 5 T1

      Test Data (score)

      Test Result

      81

      422.0

      100

      60

      95

      86
  • MAX(value)
    • Test statement 
      SELECT MAX(score) FROM T1;
    • Test data and results
      Table 6 T1

      Test Data (score)

      Test Result

      81

      100.0

      100

      60

      95

      86
  • MIN(value)
    • Test statement 
      SELECT MIN(score) FROM T1;
    • Test data and results
      Table 7 T1

      Test Data (score)

      Test Result

      81

      60.0

      100

      60

      95

      86
  • STDDEV_POP(value)
    • Test statement 
      SELECT STDDEV_POP(score) FROM T1;
    • Test data and results
      Table 8 T1

      Test Data (score)

      Test Result

      81

      13.0

      100

      60

      95

      86
  • STDDEV_SAMP(value)
    • Test statement 
      SELECT STDDEV_SAMP(score) FROM T1;
    • Test data and results
      Table 9 T1

      Test Data (score)

      Test Result

      81

      15.0

      100

      60

      95

      86
  • VAR_POP(value)
    • Test statement 
      SELECT VAR_POP(score) FROM T1;
    • Test data and results
      Table 10 T1

      Test Data (score)

      Test Result

      81

      193.0

      100

      60

      95

      86
  • VAR_SAMP(value)
    • Test statement 
      SELECT VAR_SAMP(score) FROM T1;
    • Test data and results
      Table 11 T1

      Test Data (score)

      Test Result

      81

      241.0

      100

      60

      95

      86
Parent topic: Built-In Functions