Aggregate Functions
An aggregate function evaluates a set of values and returns a single value. Except COUNT(*), aggregate functions ignore the value NULL. Aggregate functions are usually used with the GROUP BY clause.
- The result of an aggregate function is related to the data input sequence. Different data input sequences lead to different precisions of the result.
m_db=# CREATE TABLE IF NOT EXISTS t1 (name VARCHAR(20), c1 INT(100), c2 FLOAT(7,5)); CREATE TABLE m_db=# INSERT INTO t1 VALUES m_db-# ('Computer', 666,-55.155), m_db-# ('Computer', 789,-15.593), m_db-# ('Computer', 928,-53.963), m_db-# ('Computer', 666,-54.555), m_db-# ('Computer', 666,-55.555), m_db-# ('Database', 666,-55.155), m_db-# ('Database', 789,-15.593), m_db-# ('Database', 928,-53.963), m_db-# ('Database', 666,-54.555), m_db-# ('Database', 666,-55.555); INSERT 0 10 m_db=# select std(c1/c2) from t1; std ------------------- 15.02396266299967 (1 row) # ORDER BY changes the execution sequence of aggregate functions, resulting in different precisions of the result. m_db=# select std(c1/c2 order by c2) from t1; std -------------------- 15.023962662999669 (1 row) m_db=# select std(c1/c2) from t1 group by name; std -------------------- 15.023962662999669 15.023962662999669 (2 rows) # Using WITH ROLLUP also changes the execution sequence of aggregate functions, resulting in different precisions of the result. m_db=# select std(c1/c2) from t1 group by name with rollup; std -------------------- 15.02396266299967 15.023962662999669 15.02396266299967 (3 rows) - When an aggregate function is used together with GROUP BY and the derivation type is DECIMAL, GaussDB retains data with complete precision.
m_db=# SELECT avg(col_znumeric3/col_znumeric2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name; avg --------------- 1.45980942957 1.45980942957 1.45980942957 (3 rows) m_db=# SELECT sum(col_bit1/col_time2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name; sum -------- 0.0006 0.0006 0.0006 (3 rows)
AVG
AVG([ALL | DISTINCT] expr) [over_clause]
Description: Returns the average value of the input expr. NULL will be ignored. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, or DOUBLE.
Return value type:
- The type of the return value depends on the type of the input parameter. If the input parameter is an exact value (INT or DECIMAL), DECIMAL is returned. If the input parameter is an approximate value (FLOAT or DOUBLE), DOUBLE is returned.
- If all values in expr are NULL, AVG() returns NULL.
- If ALL is specified or ALL and DISTINCT are not specified, the average value of all non-null values in expr is returned.
- If DISTINCT is specified, the average value of different values in expr is returned.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT age, AVG(salary) FROM m_test GROUP BY (age); age | avg -----+-------- 1 | 1.6667 2 | (2 rows) m_db=# SELECT age, AVG(DISTINCT salary) FROM m_test GROUP BY (age); age | avg -----+-------- 1 | 1.5000 2 | (2 rows) m_db=# SELECT age, AVG(salary) OVER(PARTITION BY age ORDER by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM m_test; age | avg -----+-------- 1 | 1.0000 1 | 1.6667 1 | 1.6667 1 | 1.6667 2 | 2 | (6 rows) m_db=# DROP TABLE m_test; DROP TABLE
When the input is of the character type, the implicit conversion from the character type to the numeric type is triggered and a warning log is generated. As a result, the performance deteriorates.
m_db=# SELECT AVG('xxxx');
WARNING: Truncated incorrect double value: 'xxxx'
avg
-----
0
(1 row)
BIT_AND
BIT_AND([ALL] expr) [over_clause]
Description: Returns the bitwise AND of all values in expr, ignoring NULL. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: BIT_AND needs to use BIGINT as the parameter. Parameters of other types will be converted to BIGINT and may be truncated.
Return value type:
- The return value type is BIGINT.
- If all values in expr are NULL, BIT_AND() returns a neutral value (all bits are set to 1).
- ALL is optional and does not affect the result.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT);
CREATE TABLE
m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL);
INSERT 0 6
m_db=# SELECT * FROM m_test;
age | salary
-----+--------
1 |
1 | 1
1 | 2
1 | 2
2 |
2 |
(6 rows)
m_db=# SELECT BIT_AND(ALL salary) FROM m_test GROUP BY age;
bit_and
----------------------
0
18446744073709551615
(2 rows)
m_db=# SELECT age, BIT_AND(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
age | bit_and
-----+----------------------
1 | 1
1 | 2
1 | 2
1 | 18446744073709551615
2 | 18446744073709551615
2 | 18446744073709551615
(6 rows)
m_db=# DROP TABLE m_test;
DROP TABLE
When the input is of the character type, the implicit conversion from the character type to the numeric type is triggered and a warning log is generated. As a result, the performance deteriorates.
m_db=# SELECT BIT_AND('xxxx');
WARNING: Truncated incorrect INTEGER value: 'xxxx'
bit_and
---------
0
(1 row)
BIT_OR
BIT_OR([ALL] expr) [over_clause]
Description: Returns the bitwise OR of all values in expr, ignoring NULL. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: BIT_OR needs to use BIGINT as the parameter. Parameters of other types will be converted to BIGINT and may be truncated.
Return value type:
- The return value type is BIGINT.
- If all values in expr are NULL, BIT_OR() returns a neutral value (all bits are set to 0).
- ALL is optional and does not affect the result.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT BIT_OR(ALL salary) FROM m_test GROUP BY age; bit_or -------- 3 0 (2 rows) m_db=# SELECT age, BIT_OR(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test; age | bit_or -----+-------- 1 | 1 1 | 2 1 | 2 1 | 0 2 | 0 2 | 0 (6 rows) m_db=# DROP TABLE m_test; DROP TABLE
When the input is of the character type, the implicit conversion from the character type to the numeric type is triggered and a warning log is generated. As a result, the performance deteriorates.
m_db=# SELECT BIT_OR('xxxx');
WARNING: Truncated incorrect INTEGER value: 'xxxx'
bit_or
--------
0
(1 row)
BIT_XOR
BIT_XOR([ALL] expr) [over_clause]
Description: Returns the bitwise XOR of all values in expr, ignoring NULL. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: BIT_XOR needs to use BIGINT as the parameter. Parameters of other types will be converted to BIGINT and may be truncated.
Return value type:
- The return value type is BIGINT.
- If all values in expr are NULL, BIT_OR() returns a neutral value (all bits are set to 0).
- ALL is optional and does not affect the result.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT BIT_XOR(ALL salary) FROM m_test GROUP BY age; bit_xor --------- 1 0 (2 rows) m_db=# SELECT age, BIT_XOR(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test; age | bit_xor -----+--------- 1 | 1 1 | 2 1 | 2 1 | 0 2 | 0 2 | 0 (6 rows) m_db=# DROP TABLE m_test; DROP TABLE
When the input is of the character type, the implicit conversion from the character type to the numeric type is triggered and a warning log is generated. As a result, the performance deteriorates.
m_db=# SELECT BIT_XOR('xxxx');
WARNING: Truncated incorrect INTEGER value: 'xxxx'
bit_xor
---------
0
(1 row)
COUNT
- COUNT([ALL] expr)
Description: Returns the number of non-null values in expr.
Parameter type: The input parameter can be of any type.
Return value type:
- The return value type is BIGINT.
- If all values in expr are NULL, COUNT() returns 0.
- ALL is optional and does not affect the result.
- COUNT(*) is special. It returns the number of all values in expr, whether NULL or not.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT COUNT(ALL salary) FROM m_test GROUP BY age; count ------- 3 0 (2 rows) m_db=# SELECT COUNT(*) FROM m_test GROUP BY age; count ------- 4 2 (2 rows) m_db=# DROP TABLE m_test; DROP TABLE
- COUNT(DISTINCT expr1 [, expr2, ..., exprN] )
Description: Returns the number of different non-null values in expr.
Parameter type: The input parameter can be of any type.
Return value type:
- The return value type is BIGINT.
- If all values in expr are NULL, 0 is returned.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT COUNT(DISTINCT salary) FROM m_test GROUP BY age; count ------- 2 0 (2 rows) m_db=# DROP TABLE m_test; DROP TABLE - COUNT(expr) over_clause
Description: Returns the total number of non-NULL rows of expr partitioned based on over_clause. If the input is NULL, NULL is output. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Return value type:
- The return value type is BIGINT.
- If all values in expr are NULL, 0 is returned.
Examples:
m_db=# CREATE TABLE count_t1(group_id INT, val INT); CREATE TABLE m_db=# INSERT INTO count_t1 VALUES(1,1),(1,2),(1, 3),(2, 4),(2, 5),(2, 6),(3,7),(3,8); INSERT 0 8 m_db=# SELECT group_id, val, COUNT(val) OVER(partition by group_id) FROM count_t1; groupid | val | count ---------+-----+------- 1 | 1 | 3 1 | 2 | 3 1 | 3 | 3 2 | 4 | 3 2 | 5 | 3 2 | 6 | 3 3 | 7 | 2 3 | 8 | 2 (8 rows) m_db=# DROP TABLE count_t1; DROP TABLE
GROUP_CONCAT
The syntax of GROUP_CONCAT is as follows:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Description: Concatenates values in expr and returns a string. NULL is ignored.
- When DISTINCT is specified, duplicate values in expr are eliminated.
- ORDER BY: Sorts the values in expr by specifying a column.
- ASC | DESC: ASC indicates ascending order, and DESC indicates descending order. Ascending order is used by default.
- SEPARATOR: separator used for concatenating strings. Comma (,) is used by default.
- If both DISTINCT and ORDER BY are specified, the DISTINCT expression must contain the ORDER BY expression. Otherwise, an error is reported.
- GROUP_CONCAT(... ORDER BY Number) does not indicate the sequence of the parameter. The number is only a constant expression, which is equivalent to no sorting.
- Parameter group_concat_max_len is used to limit the maximum length of GROUP_CONCAT. If the length exceeds the maximum, the system truncates the length. Currently, the maximum length that can be returned is 1073741823.
Return type: TEXT or BLOB
Examples:
m_db=# CREATE TABLE m_test(id INT, name CHAR); CREATE TABLE m_db=# INSERT INTO m_test VALUES (1, 'a'), (1, 'b'), (2, 'd'), (2, 'c'), (3, 'a'), (3, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; id | name ----+------ 1 | a 1 | b 2 | d 2 | c 3 | a 3 | (6 rows) m_db=# SELECT GROUP_CONCAT(name SEPARATOR '') FROM m_test; group_concat -------------- abdca (1 row) m_db=# SELECT GROUP_CONCAT(DISTINCT id, name ORDER BY id DESC) FROM m_test; group_concat ------------------ 3a,2c,2d,1a,1b (1 row) m_db=# DROP TABLE m_test; DROP TABLE
MAX
MAX([ALL | DISTINCT] expr) [over_clause]
Description: Returns the maximum value of expr. NULL will be ignored. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: any number, string, or datetime.
Return value type:
- The return type is the same as the parameter data type. If the parameter is not a table column, the return type of MAX is the same as that of MySQL 8.0 but different from that of MySQL 5.7.
- If all values in expr are NULL, MAX() returns NULL.
- If ALL is specified or ALL and DISTINCT are not specified, the maximum value of all non-null values in expr is returned.
- If DISTINCT is specified, the maximum value of different non-null values in expr is returned, which is the same as the result when DISTINCT is not specified.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT MAX(DISTINCT salary) FROM m_test GROUP BY age; max ----- 2 (2 rows) m_db=# SELECT age, MAX(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test; age | max -----+----- 1 | 1 1 | 2 1 | 2 1 | 2 | 2 | (6 rows) m_db=# DROP TABLE m_test; DROP TABLE
MIN
MIN([ALL | DISTINCT] expr) [over_clause]
Description: Returns the minimum value of expr. NULL will be ignored. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: any number, string, or datetime.
Return value type:
- The return type is the same as the parameter data type. If the parameter is not a table column, the return type of MIN is the same as that of MySQL 8.0 but different from that of MySQL 5.7.
- If all values in expr are NULL, MIN() returns NULL.
- If ALL is specified or ALL and DISTINCT are not specified, the minimum value of all non-null values in expr is returned.
- If DISTINCT is specified, the minimum value of different non-null values in expr is returned, which is the same as the result when DISTINCT is not specified.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT MIN(DISTINCT salary) FROM m_test GROUP BY age; min ----- 1 (2 rows) m_db=# SELECT age, MIN(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test; age | min -----+----- 1 | 1 1 | 2 1 | 2 1 | 2 | 2 | (6 rows) m_db=# DROP TABLE m_test; DROP TABLE
SUM
SUM([ALL | DISTINCT] expr) [over_clause]
Description: Returns the sum of input expr. NULL will be ignored. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Parameter type: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, or DOUBLE.
Return value type:
- The type of the return value depends on the type of the input parameter. If the input parameter is an exact value (INT or DECIMAL), DECIMAL is returned. If the input parameter is an approximate value (FLOAT or DOUBLE), DOUBLE is returned.
- If all values in expr are NULL, SUM() returns NULL.
- If ALL is specified or ALL and DISTINCT are not specified, the sum of all non-null values in expr is returned.
- If DISTINCT is specified, the sum of different non-null values in expr is returned.
Examples:
m_db=# CREATE TABLE m_test(age INT, salary INT); CREATE TABLE m_db=# INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL); INSERT 0 6 m_db=# SELECT * FROM m_test; age | salary -----+-------- 1 | 1 | 1 1 | 2 1 | 2 2 | 2 | (6 rows) m_db=# SELECT age, SUM(salary) FROM m_test GROUP BY (age); age | sum -----+----- 1 | 5 2 | (2 rows) m_db=# SELECT age, SUM(DISTINCT salary) FROM m_test GROUP BY (age); age | sum -----+----- 1 | 3 2 | (2 rows) m_db=# SELECT age, SUM(salary) OVER(PARTITION BY age ORDER by salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM m_test; age | sum -----+----- 1 | 1 1 | 3 1 | 5 1 | 5 2 | 2 | (6 rows) m_db=# DROP TABLE m_test; DROP TABLE
When the input is of the character type, the implicit conversion from the character type to the numeric type is triggered and a warning log is generated. As a result, the performance deteriorates.
m_db=# SELECT SUM('xxxx');
WARNING: Truncated incorrect double value: 'xxxx'
sum
-----
0
(1 row)
STD
STD(str) [over_clause]
Description: Calculates the overall standard deviation of all not-null input values and returns the result. If over_clause is specified, this function is executed as a window function. For details about the over_clause parameter, see Window Functions.
Return type: DOUBLE
Examples:
m_db=# CREATE TABLE students (name VARCHAR(20) NOT NULL, math INT);
CREATE TABLE
m_db=# INSERT INTO students (name, math) VALUES('a', 80),('b', 85),('c', 90),('d', 95),('e', 88),('f', 92),('g', 86),('h', 89),('i', 91),('j', 87);
INSERT 0 10
m_db=# SELECT STD(math) AS std_math FROM students;
std_math
--------------------
3.9509492530276824
(1 row)
m_db=# SELECT name, STD(math) OVER(ORDER by math ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM students;
name | std
------+--------------------
a | 0
b | 2.5
g | 2.6246692913372693
j | 2.6925824035672505
e | 2.7856776554368228
h | 2.9107081994288277
c | 3.063944369932457
i | 3.2403703492039275
f | 3.4354721852756214
d | 3.9509492530276806
(10 rows)
m_db=# DROP TABLE students;
DROP TABLE
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
