Aggregation functions
Aggregation functions are usually used with a GROUP BY clause, but they can also be used in standard FROM-WHERE-SELECT clauses and in conditions in a GROUP BY context.
When to use aggregation functions
Aggregation functions operate in iterative contexts, processing values across multiple rows or elements. Use aggregation functions when working with queries that include GROUP BY clauses or when aggregating values from a dataset in a FROM-SELECT query.
For example, to calculate the average rating across all customer records:
FROM customer
SELECT avg(rating)You can also use aggregation functions in a condition when the expression is in the scope of a
GROUP BY context. For example:
FROM order
GROUP BY custid AS cid
SELECT {
customer_id = cid,
order_status = if (count(orderno) == 1) 'One order' ELSE IF (count(orderno) == 2) 'Two orders' ELSE 'At least three orders'
}
ORDER BY cidAggregation functions in conditions are not supported outside a GROUP BY context. For example, the following script is not supported:
FROM customer
WHERE hasValue(rating)
SELECT IF (avg(rating) > 500) 'Good customers' ELSE 'Bad customers'To distinguish aggregation functions from array functions: aggregation functions process values in an iterative context, while array functions operate on a single collection value. For array operations, see Array functions.
Additional aggregation functions
The following functions can also be used as aggregation functions: