> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/group-by.md). # GROUP BY Group result sets by one or more columns. Learn to aggregate data using functions like SUM, COUNT, and AVG in conjunction with grouping. Use the `GROUP BY` clause in a [SELECT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md) statement to group rows together that have the same value in one or more columns, or the same computed value using expressions with any [functions and operators](/docs/server/reference/sql-functions.md) except [grouping functions](/docs/server/reference/sql-functions/aggregate-functions.md). 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 [=](/docs/server/reference/sql-structure/operators/comparison-operators.md) operator. For string values, the `=` operator ignores trailing whitespace and may normalize characters and ignore case, depending on the [collation](/docs/server/reference/data-types/string-data-types/character-sets.md) 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](/docs/server/server-management/variables-and-modes/sql_mode.md) 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 column at position *n* 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](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md). The default is `ASC`. If you want the rows to be sorted by another field, you can add an explicit [ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md). If you don't want the result to be ordered, you can add [ORDER BY NULL](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md). ### Aliases in GROUP BY and HAVING `GROUP BY` and `HAVING` can refer to a select expression by the alias given to it with `AS`, as in the `winavg` examples below. `WHERE` can't, because it's evaluated before the select expressions: `WHERE winavg > 0.2` fails with `ERROR 1054 (42S22): Unknown column 'winavg' in 'WHERE'`. An alias can have the same name as a column of a table in the `FROM` clause. Then the name means different things in different clauses: * In `GROUP BY`, the name refers to the **table column**, not to the alias. MariaDB issues warning 1052, `Column '...' in GROUP BY is ambiguous`. * In `HAVING`, the name refers to the alias, unless `GROUP BY` uses the table column of the same name. In that case, the name refers to the table column, and MariaDB issues warning 1052, `Column '...' in HAVING is ambiguous`. * In [ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md#aliases-in-order-by), the name refers to the alias. This is intended behavior. To avoid surprises, don't give an alias the name of a column in the `FROM` tables. For example, the following query groups by the `category` column, not by the `color` values that the alias `category` stands for. It returns two groups, `hat` and `shirt`, and the `category` value shown for each group comes from an arbitrary row of that group: ```sql CREATE TABLE items (category VARCHAR(10), color VARCHAR(10)); INSERT INTO items VALUES ('shirt','red'), ('shirt','blue'), ('hat','red'); SELECT color AS category, COUNT(*) FROM items GROUP BY category; +----------+----------+ | category | COUNT(*) | +----------+----------+ | red | 1 | | red | 2 | +----------+----------+ SHOW WARNINGS; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1052 | Column 'category' in GROUP BY is ambiguous | +---------+------+--------------------------------------------+ ``` With an alias that doesn't clash with a column name, the query groups by color: ```sql SELECT color AS shade, COUNT(*) FROM items GROUP BY shade; +-------+----------+ | shade | COUNT(*) | +-------+----------+ | blue | 1 | | red | 2 | +-------+----------+ ``` ### WITH ROLLUP The `WITH ROLLUP` modifier adds extra rows to the result set that represent super-aggregate summaries. For a full description with examples, see [SELECT WITH ROLLUP](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-with-rollup.md). ### GROUP BY Examples Consider the following table that records how many times each user has played and won a game: ```sql CREATE TABLE plays (name VARCHAR(16), plays INT, wins INT); INSERT INTO plays VALUES ("John", 20, 5), ("Robert", 22, 8), ("Wanda", 32, 8), ("Susan", 17, 3); ``` Get a list of win counts along with a count: ```sql SELECT wins, COUNT(*) FROM plays GROUP BY wins; +------+----------+ | wins | COUNT(*) | +------+----------+ | 3 | 1 | | 5 | 1 | | 8 | 2 | +------+----------+ 3 rows in set (0.00 sec) ``` The `GROUP BY` expression can be a computed value, and can refer back to an identifier specified with `AS`. Get a list of win averages along with a count: ```sql SELECT (wins / plays) AS winavg, COUNT(*) FROM plays GROUP BY winavg; +--------+----------+ | winavg | COUNT(*) | +--------+----------+ | 0.1765 | 1 | | 0.2500 | 2 | | 0.3636 | 1 | +--------+----------+ 3 rows in set (0.00 sec) ``` You can use any [grouping function](/docs/server/reference/sql-functions/aggregate-functions.md) in the select expression. For each win average as above, get a list of the average play count taken to get that average: ```sql SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg; +--------+------------+ | winavg | AVG(plays) | +--------+------------+ | 0.1765 | 17.0000 | | 0.2500 | 26.0000 | | 0.3636 | 22.0000 | +--------+------------+ 3 rows in set (0.00 sec) ``` You can filter on aggregate information using the `HAVING` clause. The `HAVING` clause is applied after `GROUP BY` and allows you to filter on aggregate data that is not available to the `WHERE` clause. Restrict the above example to results that involve an average number of plays over 20: ```sql SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg HAVING AVG(plays) > 20; +--------+------------+ | winavg | AVG(plays) | +--------+------------+ | 0.2500 | 26.0000 | | 0.3636 | 22.0000 | +--------+------------+ 2 rows in set (0.00 sec) ``` ### See Also * [SELECT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md) * [Joins and Subqueries](/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations.md) * [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md) * [ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md) * [Common Table Expressions](/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions.md) * [SELECT WITH ROLLUP](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-with-rollup.md) * [SELECT INTO OUTFILE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-outfile.md) * [SELECT INTO DUMPFILE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-dumpfile.md) * [FOR UPDATE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/for-update.md) * [LOCK IN SHARE MODE](/docs/server/reference/sql-statements/data-manipulation/selecting-data/lock-in-share-mode.md) * [Optimizer Hints](/docs/server/ha-and-performance/optimization-and-tuning/optimizer-hints.md) *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}