∑

MariaDB Aggregate Functions

MariaDB

Complete reference for MariaDB aggregate functions covering COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT, JSON_ARRAYAGG, STDDEV, VARIANCE, and bit aggregates. Every function includes syntax, GROUP BY examples, and comparisons with MySQL and PostgreSQL aggregate behaviour. Updated for MariaDB 11.x.

20 functions

Jump to function

COUNT SUM AVG MAX MIN GROUP_CONCAT BIT_AND BIT_OR BIT_XOR STD STDDEV STDDEV_POP STDDEV_SAMP VAR_POP VAR_SAMP VARIANCE JSON_ARRAYAGG JSON_OBJECTAGG ANY_VALUE MEDIAN

What are MariaDB Aggregate Functions?

MariaDB aggregate functions compute a single result from multiple rows and are used with GROUP BY clauses. GROUP_CONCAT() is one of MariaDB's most distinctive aggregates — it concatenates grouped values into a comma-separated string and supports ORDER BY within the aggregate and a SEPARATOR clause. MariaDB also supports JSON_ARRAYAGG() and JSON_OBJECTAGG() for building JSON directly from queries, mirroring PostgreSQL's json_agg() and json_object_agg().

COUNT

→ BIGINT

Returns the number of rows matching the query, or the number of non-NULL values in a column.

Signatures

COUNT(*)

COUNT([DISTINCT] expr)

Parameters

Parameter	Type	Description
expr	ANY	Column or expression to count; * counts all rows regardless of NULLs

Examples

Count all rows including NULLs

sql

SELECT COUNT(*) FROM orders;

→-- 1500

Count non-NULL values only

sql

SELECT COUNT(amount) FROM orders;

→-- 1480

Count distinct non-NULL values

sql

SELECT COUNT(DISTINCT customer_id) FROM orders;

→-- 342

With GROUP BY

sql

SELECT dept, COUNT(*) FROM employees GROUP BY dept;

→-- grouped row counts per dept

NULL handling — COUNT(expr) ignores NULLs

sql

SELECT COUNT(NULL);

→-- 0

⚠Anti-Pattern— Confusing COUNT(*) and COUNT(col)

COUNT(col) silently skips NULLs, so it can return a smaller number than COUNT(*) without any warning.

✓ Instead: Be explicit: use COUNT(*) for total rows and COUNT(col) when you specifically want non-NULL counts.

⇄ vs PostgreSQL: PostgreSQL COUNT is identical in behavior.

SUM

→ DOUBLE / DECIMAL

Returns the sum of all non-NULL values in an expression.

Signature

SUM([DISTINCT] expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to sum

Examples

Sum all non-NULL values

sql

SELECT SUM(amount) FROM orders;

→-- 98432.50

With GROUP BY

sql

SELECT dept, SUM(salary) FROM employees GROUP BY dept;

→-- sum per dept

DISTINCT values only

sql

SELECT SUM(DISTINCT price) FROM products;

→-- sum of unique prices

Filtered with WHERE

sql

SELECT SUM(amount) FROM orders WHERE status = 'paid';

→-- filtered sum

Returns NULL when all values are NULL

sql

SELECT SUM(NULL);

→-- NULL

⚠Anti-Pattern— Assuming SUM returns 0 on empty sets

SUM of an empty set or all-NULL column returns NULL, which can cause unexpected NULLs in calculations.

✓ Instead: Use COALESCE(SUM(col), 0) to safely default to zero.

⇄ vs PostgreSQL: PostgreSQL SUM is identical in behavior.

AVG

→ DOUBLE / DECIMAL

Returns the arithmetic mean of all non-NULL values in an expression.

Signature

AVG([DISTINCT] expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to average

Examples

Average of all non-NULL scores

sql

SELECT AVG(score) FROM test_results;

→-- 78.45

With GROUP BY

sql

SELECT dept, AVG(salary) FROM employees GROUP BY dept;

→-- average salary per dept

DISTINCT values only

sql

SELECT AVG(DISTINCT price) FROM products;

→-- average of unique prices

Filtered with WHERE

sql

SELECT AVG(amount) FROM orders WHERE year = 2024;

→-- filtered average

Returns NULL when all values are NULL

sql

SELECT AVG(NULL);

→-- NULL

⚠Anti-Pattern— Treating NULLs as zeros in averages

AVG automatically skips NULLs, so the denominator is only the count of non-NULL rows, which may not reflect your intent.

✓ Instead: If NULLs represent zero values, use AVG(COALESCE(col, 0)) to include them in the divisor.

⇄ vs PostgreSQL: PostgreSQL AVG is identical; PostgreSQL also supports ORDER BY inside aggregate for ordered-set aggregates.

MAX

→ Same as input type

Returns the maximum value in an expression across all non-NULL rows.

Signature

MAX([DISTINCT] expr)

Parameters

Parameter	Type	Description
expr	ANY	Column or expression to evaluate for maximum value

Examples

Highest salary

sql

SELECT MAX(salary) FROM employees;

→-- 120000.00

Works on dates too

sql

SELECT dept, MAX(hire_date) FROM employees GROUP BY dept;

→-- most recent hire per dept

Lexicographic max on strings

sql

SELECT MAX(name) FROM customers;

→-- 'Zara Wilson'

Filtered with WHERE

sql

SELECT MAX(score) FROM results WHERE exam_id = 5;

→-- 98

Returns NULL when all values are NULL

sql

SELECT MAX(NULL);

→-- NULL

⚠Anti-Pattern— Using ORDER BY + LIMIT instead of MAX

SELECT col FROM t ORDER BY col DESC LIMIT 1 is slower and more fragile than SELECT MAX(col) FROM t.

✓ Instead: Use MAX(col) for a single maximum value; it uses index optimizations and handles NULLs correctly.

⇄ vs PostgreSQL: PostgreSQL MAX is identical.

MIN

→ Same as input type

Returns the minimum value in an expression across all non-NULL rows.

Signature

MIN([DISTINCT] expr)

Parameters

Parameter	Type	Description
expr	ANY	Column or expression to evaluate for minimum value

Examples

Lowest price

sql

SELECT MIN(price) FROM products;

→-- 0.99

Works on dates too

sql

SELECT dept, MIN(hire_date) FROM employees GROUP BY dept;

→-- earliest hire per dept

Lexicographic min on strings

sql

SELECT MIN(name) FROM customers;

→-- 'Aaron Smith'

Filtered with WHERE

sql

SELECT MIN(score) FROM results WHERE exam_id = 5;

→-- 42

Returns NULL when all values are NULL

sql

SELECT MIN(NULL);

→-- NULL

⚠Anti-Pattern— Using ORDER BY + LIMIT instead of MIN

SELECT col FROM t ORDER BY col ASC LIMIT 1 is less efficient than SELECT MIN(col) FROM t.

✓ Instead: Use MIN(col) which benefits from index lookups and handles NULLs correctly.

⇄ vs PostgreSQL: PostgreSQL MIN is identical.

GROUP_CONCAT

→ TEXT

Concatenates non-NULL values from a group into a single string with optional ordering and custom separator.

Signature

GROUP_CONCAT([DISTINCT] expr [ORDER BY col [ASC|DESC]] [SEPARATOR str])

Parameters

Parameter	Type	Description
expr	ANY	Column or expression whose values are concatenated
ORDER BY col	CLAUSE	Optional: order values before concatenation
SEPARATOR str	VARCHAR	Optional: delimiter string (default is comma)

Examples

Default comma separator

sql

SELECT dept, GROUP_CONCAT(name) FROM employees GROUP BY dept;

→-- 'Alice,Bob,Carol'

Custom separator with ORDER BY

sql

SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ' | ') FROM employees;

→-- 'Alice | Bob | Carol'

DISTINCT with ORDER BY

sql

SELECT GROUP_CONCAT(DISTINCT tag ORDER BY tag) FROM article_tags GROUP BY article_id;

→-- 'css,html,js'

Build comma list for IN clause

sql

SELECT GROUP_CONCAT(id SEPARATOR ',') FROM orders WHERE status = 'open';

→-- '1001,1002,1003'

Returns NULL when all values are NULL

sql

SELECT GROUP_CONCAT(NULL);

→-- NULL

⚠Anti-Pattern— Ignoring group_concat_max_len truncation

When concatenated output exceeds group_concat_max_len the result is silently truncated, corrupting the data without any error.

✓ Instead: Set group_concat_max_len to an appropriate size, or verify output length with CHAR_LENGTH() after the query.

◆MariaDB Note

MariaDB supports GROUP_CONCAT inside recursive CTEs.

⇄ vs PostgreSQL: PostgreSQL uses STRING_AGG(expr, separator ORDER BY ...) instead of GROUP_CONCAT.

BIT_AND

→ BIGINT UNSIGNED

Returns the bitwise AND of all non-NULL values in an integer expression.

Signature

BIT_AND(expr)

Parameters

Parameter	Type	Description
expr	INTEGER	Integer column or expression to aggregate with bitwise AND

Examples

Find permission bits common to every editor

sql

SELECT BIT_AND(flags) FROM user_permissions WHERE role = 'editor';

→-- bits set in ALL editor rows

With GROUP BY

sql

SELECT group_id, BIT_AND(perms) FROM acl GROUP BY group_id;

→-- common permission bits per group

6 AND 5 = 4

sql

SELECT BIT_AND(val) FROM (SELECT 6 val UNION ALL SELECT 5) t;

→-- 4

Identical values return that value

sql

SELECT BIT_AND(val) FROM (SELECT 15 val UNION ALL SELECT 15) t;

→-- 15

Empty set returns all-bits-set (AND identity element)

sql

SELECT BIT_AND(NULL);

→-- 18446744073709551615

⚠Anti-Pattern— Using BIT_AND to find any set bit

BIT_AND finds bits that are set in ALL rows; if you want bits set in ANY row, you need BIT_OR.

✓ Instead: Use BIT_OR to find bits present in at least one row, and BIT_AND for bits present in every row.

⇄ vs PostgreSQL: PostgreSQL uses bit_and(expr) with the same semantics.

BIT_OR

→ BIGINT UNSIGNED

Returns the bitwise OR of all non-NULL values in an integer expression.

Signature

BIT_OR(expr)

Parameters

Parameter	Type	Description
expr	INTEGER	Integer column or expression to aggregate with bitwise OR

Examples

Collect all feature flags a user ever had

sql

SELECT BIT_OR(flags) FROM user_events WHERE user_id = 42;

→-- union of all feature flags ever set

With GROUP BY

sql

SELECT group_id, BIT_OR(perms) FROM acl GROUP BY group_id;

→-- combined permission bits per group

5 OR 2 = 7

sql

SELECT BIT_OR(val) FROM (SELECT 5 val UNION ALL SELECT 2) t;

→-- 7

Aggregate status bitmask

sql

SELECT BIT_OR(status_flag) FROM orders WHERE customer_id = 10;

→-- bitmask of all order statuses for customer

Empty set returns 0 (OR identity element)

sql

SELECT BIT_OR(NULL);

→-- 0

⚠Anti-Pattern— Using BIT_OR to check that all rows have a bit set

BIT_OR returns a non-zero result if any row has the bit set, not all rows — it cannot confirm universal bit presence.

✓ Instead: Use BIT_AND to confirm a bit is set in every row.

⇄ vs PostgreSQL: PostgreSQL uses bit_or(expr) with identical semantics.

BIT_XOR

→ BIGINT UNSIGNED

Returns the bitwise XOR of all non-NULL values in an integer expression.

Signature

BIT_XOR(expr)

Parameters

Parameter	Type	Description
expr	INTEGER	Integer column or expression to aggregate with bitwise XOR

Examples

Compute running XOR checksum over a batch

sql

SELECT BIT_XOR(checksum) FROM rows WHERE batch_id = 7;

→-- parity checksum of batch

With GROUP BY

sql

SELECT batch_id, BIT_XOR(row_hash) FROM log GROUP BY batch_id;

→-- XOR fingerprint per batch

5 XOR 3 = 6

sql

SELECT BIT_XOR(val) FROM (SELECT 5 val UNION ALL SELECT 3) t;

→-- 6

XOR of identical values cancels out

sql

SELECT BIT_XOR(val) FROM (SELECT 7 val UNION ALL SELECT 7) t;

→-- 0

Empty set returns 0 (XOR identity element)

sql

SELECT BIT_XOR(NULL);

→-- 0

⚠Anti-Pattern— Relying on BIT_XOR for collision-resistant checksums

XOR is order-independent and cancels duplicate values, so two different datasets can produce the same XOR result.

✓ Instead: Use BIT_XOR only for quick change-detection hints; use cryptographic hashes (MD5, SHA2) for integrity guarantees.

⇄ vs PostgreSQL: PostgreSQL has no built-in BIT_XOR aggregate; requires a custom aggregate or manual workaround.

STD

→ DOUBLE

Returns the population standard deviation of a numeric expression (synonym for STDDEV_POP).

Signature

STD(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute standard deviation for

Examples

Population std dev of all scores

sql

SELECT STD(score) FROM exam_results;

→-- 12.34

With GROUP BY

sql

SELECT class_id, STD(grade) FROM grades GROUP BY class_id;

→-- std dev per class

Filtered with WHERE

sql

SELECT STD(salary) FROM employees WHERE dept = 'Engineering';

→-- salary spread in Engineering

Single value has zero standard deviation

sql

SELECT STD(42);

→-- 0

Returns NULL when all values are NULL

sql

SELECT STD(NULL);

→-- NULL

⚠Anti-Pattern— Using population std dev on a sample

STD/STDDEV_POP divides by N, which underestimates variability when your data is a sample from a larger population.

✓ Instead: Use STDDEV_SAMP (divides by N-1) when working with a sample rather than the full population.

⇄ vs PostgreSQL: PostgreSQL uses STDDEV_POP(expr); STD() is not available in PostgreSQL.

STDDEV

→ DOUBLE

Returns the population standard deviation of a numeric expression (synonym for STDDEV_POP and STD).

Signature

STDDEV(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute standard deviation for

Examples

Spread of response times

sql

SELECT STDDEV(response_time_ms) FROM api_logs;

→-- 45.67

With GROUP BY

sql

SELECT endpoint, STDDEV(response_time_ms) FROM api_logs GROUP BY endpoint;

→-- std dev per endpoint

Filtered with WHERE

sql

SELECT STDDEV(price) FROM products WHERE category = 'Electronics';

→-- price spread in Electronics

Classic textbook example

sql

SELECT STDDEV(val) FROM (SELECT 2 val UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 5 UNION ALL SELECT 7 UNION ALL SELECT 9) t;

→-- 2

Returns NULL when all values are NULL

sql

SELECT STDDEV(NULL);

→-- NULL

⚠Anti-Pattern— Confusing STDDEV with STDDEV_SAMP

STDDEV computes the population standard deviation (divides by N), while STDDEV_SAMP divides by N-1 for sample correction.

✓ Instead: Use STDDEV_SAMP for sample data; it applies Bessel's correction (N-1) for an unbiased estimate.

◆MariaDB Note

In MariaDB (and MySQL), STDDEV is an alias for population std dev (STDDEV_POP). In PostgreSQL, STDDEV maps to sample std dev (STDDEV_SAMP).

⇄ vs PostgreSQL: PostgreSQL STDDEV() is an alias for STDDEV_SAMP (not STDDEV_POP) — behavior differs from MariaDB on sample data.

STDDEV_POP

→ DOUBLE

Returns the population standard deviation of a numeric expression, dividing the sum of squared deviations by N.

Signature

STDDEV_POP(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute population standard deviation for

Examples

Full-population std dev

sql

SELECT STDDEV_POP(score) FROM census_data;

→-- 18.92

With GROUP BY

sql

SELECT region, STDDEV_POP(income) FROM census_data GROUP BY region;

→-- income spread per region

Filtered with WHERE

sql

SELECT STDDEV_POP(temperature) FROM weather WHERE month = 7;

→-- July temperature spread

Compare both measures

sql

SELECT STDDEV_POP(x), STDDEV_SAMP(x) FROM measurements;

→-- population vs sample std dev side by side

Returns NULL when all values are NULL

sql

SELECT STDDEV_POP(NULL);

→-- NULL

⚠Anti-Pattern— Using STDDEV_POP on sampled data

When rows are a sample, STDDEV_POP slightly underestimates the true population standard deviation.

✓ Instead: Use STDDEV_SAMP for sample data; it applies Bessel's correction (N-1) for an unbiased estimate.

⇄ vs PostgreSQL: PostgreSQL STDDEV_POP is identical in behavior.

STDDEV_SAMP

→ DOUBLE

Returns the sample standard deviation of a numeric expression, dividing by N-1 (Bessel's correction).

Signature

STDDEV_SAMP(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute sample standard deviation for

Examples

Sample std dev of patient heights

sql

SELECT STDDEV_SAMP(height_cm) FROM sample_patients;

→-- 8.76

With GROUP BY

sql

SELECT lab_id, STDDEV_SAMP(measurement) FROM lab_results GROUP BY lab_id;

→-- spread per lab

Filtered with WHERE

sql

SELECT STDDEV_SAMP(price) FROM sampled_products;

→-- estimated price spread

Two-row sample

sql

SELECT STDDEV_SAMP(val) FROM (SELECT 10 val UNION ALL SELECT 20) t;

→-- 7.07

Returns NULL for single-row sample (N-1 = 0)

sql

SELECT STDDEV_SAMP(val) FROM (SELECT 42 val) t;

→-- NULL

⚠Anti-Pattern— Expecting a result from a single-row sample

STDDEV_SAMP with only one row returns NULL because the variance formula is undefined for N=1.

✓ Instead: Add a HAVING COUNT(*) > 1 guard when using STDDEV_SAMP per group to avoid NULL results from singleton groups.

⇄ vs PostgreSQL: PostgreSQL STDDEV_SAMP is identical; PostgreSQL's plain STDDEV() is also an alias for STDDEV_SAMP (unlike MariaDB where STDDEV = STDDEV_POP).

VAR_POP

→ DOUBLE

Returns the population variance of a numeric expression, computed as the mean of squared deviations divided by N.

Signature

VAR_POP(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute population variance for

Examples

Population variance of all scores

sql

SELECT VAR_POP(score) FROM exam_results;

→-- 152.33

With GROUP BY

sql

SELECT dept, VAR_POP(salary) FROM employees GROUP BY dept;

→-- salary variance per dept

Financial volatility analysis

sql

SELECT VAR_POP(daily_return) FROM stock_prices WHERE ticker = 'AAPL';

→-- price volatility measure

Variance is the square of std dev

sql

SELECT SQRT(VAR_POP(score)) AS stddev_pop FROM exam_results;

→-- same as STDDEV_POP(score)

Returns NULL when all values are NULL

sql

SELECT VAR_POP(NULL);

→-- NULL

⚠Anti-Pattern— Using VAR_POP for sample data

VAR_POP divides by N, which gives a biased (too-small) variance estimate when your data is a sample.

✓ Instead: Use VAR_SAMP (divides by N-1) for sample data to get an unbiased variance estimate.

⇄ vs PostgreSQL: PostgreSQL VAR_POP is identical in behavior.

VAR_SAMP

→ DOUBLE

Returns the sample variance of a numeric expression, computed as the mean of squared deviations divided by N-1.

Signature

VAR_SAMP(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute sample variance for

Examples

Sample variance of weights

sql

SELECT VAR_SAMP(weight_kg) FROM sample_subjects;

→-- 234.56

With GROUP BY

sql

SELECT group_id, VAR_SAMP(result) FROM experiments GROUP BY group_id;

→-- variance per experiment group

Filtered with WHERE

sql

SELECT VAR_SAMP(latency_ms) FROM performance_tests WHERE server = 'web01';

→-- latency variability estimate

Small sample example

sql

SELECT VAR_SAMP(x) FROM (SELECT 2 x UNION ALL SELECT 4 UNION ALL SELECT 6) t;

→-- 4

Returns NULL for single-row input (N-1 = 0)

sql

SELECT VAR_SAMP(val) FROM (SELECT 42 val) t;

→-- NULL

⚠Anti-Pattern— Confusing VAR_SAMP with VARIANCE

VARIANCE is an alias for VAR_POP in MariaDB, not VAR_SAMP — using them interchangeably leads to the wrong denominator.

✓ Instead: Explicitly use VAR_SAMP for sample variance and VAR_POP or VARIANCE for population variance.

⇄ vs PostgreSQL: PostgreSQL VAR_SAMP is identical; PostgreSQL's plain VARIANCE() is an alias for VAR_SAMP (unlike MariaDB where VARIANCE = VAR_POP).

VARIANCE

→ DOUBLE

Returns the population variance of a numeric expression (synonym for VAR_POP).

Signature

VARIANCE(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression to compute population variance for

Examples

Population variance of scores

sql

SELECT VARIANCE(score) FROM exam_results;

→-- 152.33

With GROUP BY

sql

SELECT class_id, VARIANCE(grade) FROM grades GROUP BY class_id;

→-- variance per class

Filtered with WHERE

sql

SELECT VARIANCE(price) FROM products WHERE in_stock = 1;

→-- price spread for in-stock items

VARIANCE is an alias for VAR_POP

sql

SELECT VARIANCE(val), VAR_POP(val) FROM measurements;

→-- identical results

Returns NULL when all values are NULL

sql

SELECT VARIANCE(NULL);

→-- NULL

⚠Anti-Pattern— Assuming VARIANCE equals VAR_SAMP across databases

In PostgreSQL, VARIANCE() is an alias for VAR_SAMP, but in MariaDB it aliases VAR_POP — the same function name yields different results across databases.

✓ Instead: Use the explicit names VAR_POP or VAR_SAMP in cross-database code to avoid portability bugs.

◆MariaDB Note

VARIANCE is an alias for VAR_POP in MariaDB.

⇄ vs PostgreSQL: PostgreSQL VARIANCE() is an alias for VAR_SAMP, not VAR_POP — results differ on sample data.

JSON_ARRAYAGG

MariaDB 10.5.0+→ JSON (LONGTEXT)

Aggregates values from a column into a JSON array.

Signature

JSON_ARRAYAGG(expr)

Parameters

Parameter	Type	Description
expr	ANY	Column or expression whose values are collected into a JSON array

Examples

Collect names into a JSON array

sql

SELECT JSON_ARRAYAGG(name) FROM products WHERE category_id = 3;

→-- '["Widget","Gadget","Gizmo"]'

With GROUP BY

sql

SELECT category_id, JSON_ARRAYAGG(name) FROM products GROUP BY category_id;

→-- JSON array of names per category

With ORDER BY inside the aggregate

sql

SELECT JSON_ARRAYAGG(price ORDER BY price DESC) FROM products;

→-- '[29.99,19.99,9.99]'

Nested JSON objects inside the array

sql

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) FROM products;

→-- '[{"id":1,"name":"Widget"},...]'

NULL values are included as JSON null

sql

SELECT JSON_ARRAYAGG(NULL);

→-- '[null]'

⚠Anti-Pattern— Using GROUP_CONCAT for structured data

GROUP_CONCAT produces a plain string that requires manual parsing; any value containing the separator character breaks the result.

✓ Instead: Use JSON_ARRAYAGG when downstream consumers need a structured JSON array that handles special characters safely.

◆MariaDB Note

Available since MariaDB 10.5.0.

⇄ vs PostgreSQL: PostgreSQL uses json_agg(expr) or jsonb_agg(expr); ORDER BY inside aggregate requires PostgreSQL 9.4+.

JSON_OBJECTAGG

MariaDB 10.5.0+→ JSON (LONGTEXT)

Aggregates key-value pairs from two columns into a JSON object.

Signature

JSON_OBJECTAGG(key_expr, value_expr)

Parameters

Parameter	Type	Description
key_expr	VARCHAR	Expression providing the JSON object key (must resolve to a string)
value_expr	ANY	Expression providing the JSON object value

Examples

Build a code-to-name lookup object

sql

SELECT JSON_OBJECTAGG(code, name) FROM countries;

→-- '{"US":"United States","GB":"United Kingdom"}'

With GROUP BY

sql

SELECT dept, JSON_OBJECTAGG(emp_id, salary) FROM employees GROUP BY dept;

→-- salary map per dept

Serialize settings as a JSON object

sql

SELECT JSON_OBJECTAGG(setting_key, setting_value) FROM user_settings WHERE user_id = 1;

→-- '{"theme":"dark","lang":"en"}'

Duplicate keys: last value wins

sql

SELECT JSON_OBJECTAGG(k, v) FROM (SELECT 'a' k, 1 v UNION ALL SELECT 'a', 2) t;

→-- '{"a":2}'

NULL values become JSON null

sql

SELECT JSON_OBJECTAGG(k, NULL) FROM (SELECT 'x' k) t;

→-- '{"x":null}'

⚠Anti-Pattern— Relying on key order or deduplication

JSON_OBJECTAGG does not guarantee key order, and duplicate keys silently drop earlier values.

✓ Instead: Ensure key uniqueness before aggregation; if order matters, pre-sort in a subquery (though JSON object key order is not guaranteed by spec).

◆MariaDB Note

Available since MariaDB 10.5.0.

⇄ vs PostgreSQL: PostgreSQL uses json_object_agg(key, value) or jsonb_object_agg(key, value).

ANY_VALUE

MariaDB 10.3.3+→ Same as input type

Returns an arbitrary value from the group for a non-aggregated column, suppressing ONLY_FULL_GROUP_BY errors.

Signature

ANY_VALUE(expr)

Parameters

Parameter	Type	Description
expr	ANY	Column or expression for which any representative value from the group is acceptable

Examples

Suppress ONLY_FULL_GROUP_BY for non-deterministic column

sql

SELECT dept, ANY_VALUE(manager_name), SUM(salary) FROM employees GROUP BY dept;

→-- one manager name per dept (arbitrary)

Acceptable when all emails per customer are the same

sql

SELECT customer_id, ANY_VALUE(email), COUNT(*) FROM orders GROUP BY customer_id;

→-- one email per customer

Single-group usage

sql

SELECT ANY_VALUE(name) FROM products WHERE category = 'Books';

→-- some book name

Pick a representative thumbnail

sql

SELECT category, ANY_VALUE(image_url) FROM products GROUP BY category;

→-- a representative image per category

Returns NULL when all values are NULL

sql

SELECT ANY_VALUE(NULL);

→-- NULL

⚠Anti-Pattern— Using ANY_VALUE to silence errors on non-uniform data

ANY_VALUE suppresses the ONLY_FULL_GROUP_BY error but silently returns an arbitrary value when the column is not functionally dependent on the GROUP BY key.

✓ Instead: Use MIN(), MAX(), or a subquery to get a deterministic value; only use ANY_VALUE when all group values are truly equivalent.

◆MariaDB Note

Primarily useful when sql_mode includes ONLY_FULL_GROUP_BY.

⇄ vs PostgreSQL: PostgreSQL enforces standard GROUP BY rules and has no ANY_VALUE; use DISTINCT ON or a subquery instead.

MEDIAN

MariaDB 10.3.3+→ DOUBLE

Returns the median (middle value) of a sorted numeric expression within a group.

Signature

MEDIAN(expr)

Parameters

Parameter	Type	Description
expr	NUMERIC	Numeric column or expression for which the median is computed

Examples

Median salary across all employees

sql

SELECT MEDIAN(salary) FROM employees;

→-- 65000.00

With GROUP BY

sql

SELECT dept, MEDIAN(salary) FROM employees GROUP BY dept;

→-- median salary per dept

Filtered with WHERE

sql

SELECT MEDIAN(response_time_ms) FROM api_logs WHERE endpoint = '/api/search';

→-- median latency — robust to outliers

Compare mean and median side by side

sql

SELECT AVG(salary) AS mean_salary, MEDIAN(salary) AS median_salary FROM employees;

→-- compare mean vs median for skew analysis

Returns NULL when all values are NULL

sql

SELECT MEDIAN(NULL);

→-- NULL

⚠Anti-Pattern— Using AVG as a proxy for the median

AVG is heavily influenced by extreme outliers; in skewed distributions (income, response times) the average can be significantly higher than the typical value.

✓ Instead: Use MEDIAN for a better measure of central tendency in skewed data, and AVG only when the distribution is approximately symmetric.

◆MariaDB Note

MEDIAN is a MariaDB-only extension not available in MySQL.

⇄ vs MySQL: MySQL does not have a MEDIAN aggregate function; requires a workaround with user variables or subqueries.

⇄ vs PostgreSQL: PostgreSQL uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY expr) to compute the median.

Related MariaDB Categories

Window Functions →JSON Functions →String Functions →