Updated on 2025-09-09 GMT+08:00

Aggregate Functions

Table 1 Aggregate functions

MySQL

GaussDB

Difference

AVG()

Supported, with differences.

  • If DISTINCT is specified and the SQL statement contains the GROUP BY clause, GaussDB does not sort the results, while MySQL sorts the results.
  • In GaussDB, if the columns in expr are of the BIT, BOOL, or integer type and the sum of all rows exceeds the range of BIGINT, overflow occurs, reversing integers.

BIT_AND()

Supported.

-

BIT_OR()

Supported.

-

BIT_XOR()

Supported.

-

COUNT()

Supported, with differences.

If DISTINCT is specified and the SQL statement contains the GROUP BY clause, GaussDB does not sort the results, while MySQL sorts the results.

GROUP_CONCAT()

Supported, with differences.

  • If DISTINCT is specified and the SQL statement contains the GROUP BY clause, GaussDB does not sort the results, while MySQL sorts the results.
  • If GROUP_CONCAT is to return a binary type, only the BLOB type is returned. In other cases, the TEXT type is returned. MySQL may return the LONGTEXT, TINYTEXT, LONGBLOB, or TINYBLOB type based on the return length.
  • In GaussDB, if the parameters in GROUP_CONCAT contain both the DISTINCT and ORDER BY syntaxes, all expressions following ORDER BY must be in the DISTINCT expression.
  • In GaussDB, 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.
  • In GaussDB, the group_concat_max_len parameter is used to limit the maximum return length of GROUP_CONCAT. If the return length exceeds the maximum, the length is truncated. Currently, the maximum length that can be returned is 1073741823, which is smaller than that in MySQL.

MAX()

Supported, with differences.

  • If DISTINCT is specified and the SQL statement contains the GROUP BY clause, GaussDB does not sort the results, while MySQL sorts the results.
  • When the parameter is not a table column, the return value types of the MAX and MIN functions are different from those in MySQL 5.7.
  • When the parameter is of the FLOAT type, the return values of the MAX and MIN functions are the same as those in MySQL 5.7. The behavior of MySQL 5.7 and MySQL 8.0 is different. As a result, the return values of the MAX and MIN functions with CAST(expr AS FLOAT[(p)]) nested are different from those in MySQL 8.0.
    -- GaussDB:
    m_db=# CREATE TABLE t1(c1 float);
    CREATE TABLE
    
    m_db=# INSERT INTO t1 VALUES(1.2);
    INSERT 0 1
    
    m_db=# SELECT MAX(c1) FROM t1;
            max         
    --------------------
     1.2000000476837158
    (1 row)
    
    m_db=# SELECT MAX(CAST(1.2 AS FLOAT));
            max         
    --------------------
     1.2000000476837158
    (1 row)
    
    m_db=# DROP TABLE t1;
    DROP TABLE
    
    -- MySQL 5.7:
    mysql> CREATE TABLE t1(c1 float);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> INSERT INTO t1 VALUES(1.2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> SELECT MAX(c1) FROM t1;
    +--------------------+
    | MAX(c1)            |
    +--------------------+
    | 1.2000000476837158 |
    +--------------------+
    1 row in set (0.00 sec)
    -- MySQL 5.7 does not support the CAST(expr AS FLOAT[(p)]) expression.
    mysql> SELECT MAX(CAST(1.2 AS FLOAT));
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FLOAT))' at line 1 
    
    mysql> DROP TABLE t1;
    Query OK, 0 rows affected (0.01 sec)
    
    -- MySQL 8.0:
    mysql> CREATE TABLE t1(c1 float);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> INSERT INTO t1 VALUES(1.2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> SELECT MAX(c1) FROM t1;
    +---------+
    | MAX(c1) |
    +---------+
    |     1.2 |
    +---------+
    1 row in set (0.00 sec)
    
    mysql> SELECT MAX(CAST(1.2 AS FLOAT));
    +-------------------------+
    | MAX(CAST(1.2 AS FLOAT)) |
    +-------------------------+
    |                     1.2 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    mysql> DROP TABLE t1;
    Query OK, 0 rows affected (0.01 sec)

MIN()

SUM()

Supported, with differences.

  • If DISTINCT is specified and the SQL statement contains the GROUP BY clause, GaussDB does not sort the results, while MySQL sorts the results.
  • In GaussDB, if the columns in expr are of the BIT, BOOL, or integer type and the sum of all rows exceeds the range of BIGINT, overflow occurs, reversing integers.