mariadb-aggregate-functions

GROUP_CONCAT accepts DISTINCT, ORDER BY, SEPARATOR, and LIMIT inside the call, for example GROUP_CONCAT(DISTINCT x ORDER BY y SEPARATOR '; ' LIMIT 10). The default separator is a comma, and output is truncated at group_concat_max_len (default 1 MB).

COUNT(*) counts all rows including those with NULLs, while COUNT(expr) counts only non-NULL values of the expression. COUNT(DISTINCT expr) counts distinct non-NULL values, so all three can return different results on the same column.

STD, STDDEV, and STDDEV_POP all return the population standard deviation (dividing by N). For the sample standard deviation (dividing by N-1), use STDDEV_SAMP; similarly VAR_SAMP gives sample variance.

MariaDB's default sql_mode does not include ONLY_FULL_GROUP_BY, so selecting non-grouped columns runs without error and returns an arbitrary row's value per group. Add the column to GROUP BY or enable ONLY_FULL_GROUP_BY to make it an error.

On no matching rows, SUM, AVG, MIN, MAX, GROUP_CONCAT, and the STDDEV/VAR family return NULL; only COUNT returns 0. All aggregates except COUNT(*) skip NULL rows during calculation.

Most aggregates work with OVER(), including COUNT, SUM, AVG, MIN, MAX, BIT_*, and the statistics family. However, SUM and AVG with DISTINCT cannot be window functions, and neither can JSON_ARRAYAGG or JSON_OBJECTAGG.