On this page

Show all

Help Center/ Data Lake Insight/ SQL Syntax Reference (Kuala Lumpur Region)/ Historical Versions/ Flink SQL Syntax/ Built-In Functions/ Aggregate Functions

Aggregate Functions

Updated on 2025-01-22 GMT+08:00

View PDF

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

Previous topic: Type Conversion Functions

Next topic: Table-Valued Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot