Aggregate Functions

AVG

AVG([ALL | DISTINCT] expr)

Description: Returns the average value of the input expr. NULL will be ignored.

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) DISTRIBUTE BY HASH(age);
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=# DROP TABLE m_test;
DROP TABLE

BIT_AND

BIT_AND([ALL] expr)

Description: Returns the bitwise AND of all values in expr, ignoring NULL.

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) DISTRIBUTE BY HASH(age);
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
----------------------
 18446744073709551615
 0
(2 rows)

m_db=# DROP TABLE m_test;
DROP TABLE

BIT_OR

BIT_OR([ALL] expr)

Description: Returns the bitwise OR of all values in expr, ignoring NULL.

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) DISTRIBUTE BY HASH(age);
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
--------
 0
 3
(2 rows)

m_db=# DROP TABLE m_test;
DROP TABLE

BIT_XOR

BIT_XOR([ALL] expr)

Description: Returns the bitwise XOR of all values in expr, ignoring NULL.

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) DISTRIBUTE BY HASH(age);
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
---------
 0
 1
(2 rows)

m_db=# DROP TABLE m_test;
DROP TABLE

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) DISTRIBUTE BY HASH(age);
  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

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 value type: TEXT

Examples:

m_db=# CREATE TABLE m_test(id INT, name CHAR) DISTRIBUTE BY HASH(id);
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)

Description: Returns the maximum value of expr. NULL will be ignored.

Parameter type: any number, string, or datetime.

Return value type:

• Return value type: same as the parameter type
• 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) DISTRIBUTE BY HASH(age);
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=# DROP TABLE m_test;
DROP TABLE

MIN

MIN([ALL | DISTINCT] expr)

Description: Returns the minimum value of expr. NULL will be ignored.

Parameter type: any number, string, or datetime.

Return value type:

• Return value type: same as the parameter type
• 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) DISTRIBUTE BY HASH(age);
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=# DROP TABLE m_test;
DROP TABLE

SUM

SUM([ALL | DISTINCT] expr)

Description: Returns the sum of input expr. NULL will be ignored.

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) DISTRIBUTE BY HASH(age);
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=# DROP TABLE m_test;
DROP TABLE