Summary for Aggregate Functions

A SELECT statement with an aggregation function results in a table with a single row. However, we can think of an aggregation function as an operation that transforms a set of$n$values into a single value. The type of input values can differ from the type of output value, depending on the definition of the aggregation function. For example, the summation (i.e., SUM()) is an aggregation function, as is the maximum (i.e., MAX()).

Using GROUP BY with the WITH ROLLUP modifier

MySQL provides a modifier called WITH ROLLUP, which is syntactically a suffix to the GROUP BY clause. The modifier causes the result of the enclosing SELECT statement to feature an additional row whose value results from applying the SELECT expression to the remaining row values. The value of the expression used in the GROUP BY clause for the additional aggregation row is NULL. This is not an error but rather an indication that this row does not represent a group but is an aggregation of all groups.

The GROUPING() function

As there are scenarios in which it is hard to distinguish non-aggregating rows from aggregate rows in a statement’s output, MySQL provides the GROUPING() function. Unlike the WITH ROLLUP modifier, GROUPING() is not part of the GROUP BY clause but is available in the SELECT’s list of expressions, the HAVING clause, and the ORDER BY clause. As a function, GROUPING(first_expression, [second_expression, …]) takes at least one expression (i.e., first_expression), which has to be an exact match with one of the expressions from the corresponding GROUP BY clause. The function yields 1 for each expression if the row value in the corresponding column is NULL, representing an aggregation row. Otherwise, GROUPING() returns 0.