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

**Table 2** T1¶
Test Data (score)	Test Result
81	5
100
60
95
86

**Table 3** T1¶
content (STRING)	Test Result
"hello1 "	2
"hello2 "
"hello2"
null
86

**Table 4** T1¶
Test Data (score)	Test Result
81	84.0
100
60
95
86

**Table 5** T1¶
Test Data (score)	Test Result
81	422.0
100
60
95
86

**Table 6** T1¶
Test Data (score)	Test Result
81	100.0
100
60
95
86

**Table 7** T1¶
Test Data (score)	Test Result
81	60.0
100
60
95
86

**Table 8** T1¶
Test Data (score)	Test Result
81	13.0
100
60
95
86

**Table 9** T1¶
Test Data (score)	Test Result
81	15.0
100
60
95
86

**Table 10** T1¶
Test Data (score)	Test Result
81	193.0
100
60
95
86

**Table 11** T1¶
Test Data (score)	Test Result
81	241.0
100
60
95
86

last updated: 2024-11-20 15:34 UTC - commit: c856450509a511f9057fb9577287974ed6fce406