GROUP BY
Use the GROUP BY
clause to group rows together that have the same value in
one or more column, or the same computed value using expressions with any
functions and operators except
grouping functions. When you
use a GROUP BY
clause, you will get a single result row for each group of rows
that have the same value for the expression given in GROUP BY
.
When grouping rows, grouping values are compared as if by the =
operator.
For string values, the =
operator ignores trailing whitespace and may normalize
characters and ignore case, depending on the collation in use.
You can use any of the grouping functions in your select expression. Their values will
be calculated based on all the rows that have been grouped together for each result
row. If you select a non-grouped column or a value computed from a non-grouped
column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY
SQL_MODE is used.
You can use multiple expressions in the GROUP BY
clause, separated by commas.
Rows are grouped together if they match on each of the expressions.
You can also use a single integer as the grouping expression. If you use an integer n, the results will be grouped by the nth column in the select expression.
The WHERE
clause is applied before the GROUP BY
clause. It filters non-aggregated
rows before the rows are grouped together. To filter grouped rows based on aggregate values,
use the HAVING
clause. The HAVING
clause takes any expression and evaluates it as
a boolean, just like the WHERE
clause. You can use grouping functions in the HAVING
clause. As with the select expression, if you reference non-grouped columns in the HAVING
clause, the behavior is undefined.
By default, if a GROUP BY
clause is present, the rows in the output will be sorted by the expressions used in the GROUP BY
. You can also specify ASC
or DESC
(ascending, descending) after those expressions, like in ORDER BY
. The default is ASC
.
If you want the rows to be sorted by another field, you can add an explicit ORDER BY
. If you don't want the result to be ordered, you can add ORDER BY NULL
.