2.14.1 Aggregate Functions

2.14.1.1 GROUP BY Modifiers

The following table shows supported aggregate functions. The VARLEN Support column identifies functions that support variable-length encoded string columns. See Section 2.8.1, “Encoding String Columns”.

Table 2.2 Aggregate (GROUP BY) Functions

Name	VARLEN Support	Description
AVG()		Return the average value of the argument
COUNT()	Yes	Return a count of the number of rows returned
COUNT(DISTINCT)		Return the count of a number of different values
GROUP_CONCAT()	Yes	Return a concatenated string. See Section 2.19.3, “Functions and Operator Limitations”.
HLL()	Yes	Only available in HeatWave MySQL. Similar to COUNT(DISTINCT), but faster and with user defined precision.
JSON_ARRAYAGG()		Return result set as a single JSON array. Supported as of MySQL 9.0.0.
JSON_OBJECTAGG()		Return result set as a single JSON object literal. Supported as of MySQL 9.0.0.
MAX()	Yes	Return the maximum value
MIN()	Yes	Return the minimum value
STD()		Return the population standard deviation
STDDEV()		Return the population standard deviation
STDDEV_POP()		Return the population standard deviation
STDDEV_SAMP()		Return the sample standard deviation
SUM()		Return the sum
VAR_POP()		Return the population standard variance
VAR_SAMP()		Return the sample variance
VARIANCE()		Return the population standard variance

Name	VARLEN Support	Description

• HLL(expr,[expr...], precision)

  Returns an approximate count of the number of rows with different non-NULL expr values. 4 ≤ precision ≤ 15. The default value is 10.

  If there are no matching rows, HLL() returns 0.

  As of MySQL 8.4.0, HyperLogLog, HLL(), is available in the HeatWave primary and secondary engines. Before MySQL 8.4.0, HyperLogLog, HLL(), is only available in the HeatWave secondary engine. It is similar to COUNT(DISTINCT), but with user defined precision.

  HLL() is faster than COUNT(DISTINCT). The greater the precision, the greater the accuracy, and the greater the amount of memory required.

  For each GROUP, HLL() requires 2^precision bytes of memory. The default value of 10 requires 1KiB of memory per GROUP.

  An example with precision set to 8 that uses 256 bytes of memory per GROUP:

  Press CTRL+C to copy
   
  mysql> SELECT HLL(results, 8) FROM student;