Rechercher dans le manuel MySQL
12.20.2 GROUP BY Modifiers
The GROUP BY
clause permits a WITH
ROLLUP
modifier that causes summary output to include
extra rows that represent higher-level (that is,
super-aggregate) summary operations. ROLLUP
thus enables you to answer questions at multiple levels of
analysis with a single query. For example,
ROLLUP
can be used to provide support for
OLAP (Online Analytical Processing) operations.
Suppose that a sales
table has
year
, country
,
product
, and profit
columns for recording sales profitability:
To summarize table contents per year, use a simple
GROUP BY
like this:
- FROM sales
- +------+--------+
- +------+--------+
- | 2000 | 4525 |
- | 2001 | 3010 |
- +------+--------+
The output shows the total (aggregate) profit for each year. To
also determine the total profit summed over all years, you must
add up the individual values yourself or run an additional
query. Or you can use ROLLUP
, which provides
both levels of analysis with a single query. Adding a
WITH ROLLUP
modifier to the GROUP
BY
clause causes the query to produce another
(super-aggregate) row that shows the grand total over all year
values:
- FROM sales
- +------+--------+
- +------+--------+
- | 2000 | 4525 |
- | 2001 | 3010 |
- +------+--------+
The NULL
value in the year
column identifies the grand total super-aggregate line.
ROLLUP
has a more complex effect when there
are multiple GROUP BY
columns. In this case,
each time there is a change in value in any but the last
grouping column, the query produces an extra super-aggregate
summary row.
For example, without ROLLUP
, a summary of the
sales
table based on year
,
country
, and product
might
look like this, where the output indicates summary values only
at the year/country/product level of analysis:
- FROM sales
- +------+---------+------------+--------+
- +------+---------+------------+--------+
- | 2000 | Finland | Computer | 1500 |
- | 2000 | Finland | Phone | 100 |
- | 2000 | India | Calculator | 150 |
- | 2000 | India | Computer | 1200 |
- | 2000 | USA | Calculator | 75 |
- | 2000 | USA | Computer | 1500 |
- | 2001 | Finland | Phone | 10 |
- | 2001 | USA | Calculator | 50 |
- | 2001 | USA | Computer | 2700 |
- | 2001 | USA | TV | 250 |
- +------+---------+------------+--------+
With ROLLUP
added, the query produces several
extra rows:
- FROM sales
- +------+---------+------------+--------+
- +------+---------+------------+--------+
- | 2000 | Finland | Computer | 1500 |
- | 2000 | Finland | Phone | 100 |
- | 2000 | India | Calculator | 150 |
- | 2000 | India | Computer | 1200 |
- | 2000 | USA | Calculator | 75 |
- | 2000 | USA | Computer | 1500 |
- | 2001 | Finland | Phone | 10 |
- | 2001 | USA | Calculator | 50 |
- | 2001 | USA | Computer | 2700 |
- | 2001 | USA | TV | 250 |
- +------+---------+------------+--------+
Now the output includes summary information at four levels of analysis, not just one:
Following each set of product rows for a given year and country, an extra super-aggregate summary row appears showing the total for all products. These rows have the
product
column set toNULL
.Following each set of rows for a given year, an extra super-aggregate summary row appears showing the total for all countries and products. These rows have the
country
andproducts
columns set toNULL
.Finally, following all other rows, an extra super-aggregate summary row appears showing the grand total for all years, countries, and products. This row has the
year
,country
, andproducts
columns set toNULL
.
The NULL
indicators in each super-aggregate
row are produced when the row is sent to the client. The server
looks at the columns named in the GROUP BY
clause following the leftmost one that has changed value. For
any column in the result set with a name that matches any of
those names, its value is set to NULL
. (If
you specify grouping columns by column position, the server
identifies which columns to set to NULL
by
position.)
Because the NULL
values in the
super-aggregate rows are placed into the result set at such a
late stage in query processing, you can test them as
NULL
values only in the select list or
HAVING
clause. You cannot test them as
NULL
values in join conditions or the
WHERE
clause to determine which rows to
select. For example, you cannot add WHERE product IS
NULL
to the query to eliminate from the output all but
the super-aggregate rows.
The NULL
values do appear as
NULL
on the client side and can be tested as
such using any MySQL client programming interface. However, at
this point, you cannot distinguish whether a
NULL
represents a regular grouped value or a
super-aggregate value. To test the distinction, use the
GROUPING()
function, described
later.
Previously, MySQL did not allow the use of
DISTINCT
or ORDER BY
in a
query having a WITH ROLLUP
option. This
restriction is lifted in MySQL 8.0.12 and later. (Bug #87450,
Bug #86311, Bug #26640100, Bug #26073513)
For GROUP BY ... WITH ROLLUP
queries, to test
whether NULL
values in the result represent
super-aggregate values, the
GROUPING()
function is available
for use in the select list, HAVING
clause,
and (as of MySQL 8.0.12) ORDER BY
clause. For
example, GROUPING(year)
returns 1
when NULL
in the year
column occurs in a super-aggregate row, and 0 otherwise.
Similarly, GROUPING(country)
and
GROUPING(product)
return 1 for
super-aggregate NULL
values in the
country
and product
columns, respectively:
- mysql> SELECT
- FROM sales
- +------+---------+------------+--------+----------+-------------+-------------+
- +------+---------+------------+--------+----------+-------------+-------------+
- | 2000 | Finland | Computer | 1500 | 0 | 0 | 0 |
- | 2000 | Finland | Phone | 100 | 0 | 0 | 0 |
- | 2000 | India | Calculator | 150 | 0 | 0 | 0 |
- | 2000 | India | Computer | 1200 | 0 | 0 | 0 |
- | 2000 | USA | Calculator | 75 | 0 | 0 | 0 |
- | 2000 | USA | Computer | 1500 | 0 | 0 | 0 |
- | 2001 | Finland | Phone | 10 | 0 | 0 | 0 |
- | 2001 | USA | Calculator | 50 | 0 | 0 | 0 |
- | 2001 | USA | Computer | 2700 | 0 | 0 | 0 |
- | 2001 | USA | TV | 250 | 0 | 0 | 0 |
- +------+---------+------------+--------+----------+-------------+-------------+
Instead of displaying the
GROUPING()
results directly, you
can use GROUPING()
to substitute
labels for super-aggregate NULL
values:
- mysql> SELECT
- FROM sales
- +-----------+---------------+--------------+--------+
- +-----------+---------------+--------------+--------+
- | 2000 | Finland | Computer | 1500 |
- | 2000 | Finland | Phone | 100 |
- | 2000 | India | Calculator | 150 |
- | 2000 | India | Computer | 1200 |
- | 2000 | USA | Calculator | 75 |
- | 2000 | USA | Computer | 1500 |
- | 2001 | Finland | Phone | 10 |
- | 2001 | USA | Calculator | 50 |
- | 2001 | USA | Computer | 2700 |
- | 2001 | USA | TV | 250 |
- +-----------+---------------+--------------+--------+
With multiple expression arguments,
GROUPING()
returns a result
representing a bitmask the combines the results for each
expression, with the lowest-order bit corresponding to the
result for the rightmost expression. For example,
GROUPING(year, country, product)
is evaluated like this:
result for GROUPING(product)
+ result for GROUPING(country) << 1
+ result for GROUPING(year) << 2
The result of such a GROUPING()
is nonzero if any of the expressions represents a
super-aggregate NULL
, so you can return only
the super-aggregate rows and filter out the regular grouped rows
like this:
- FROM sales
- +------+---------+---------+--------+
- +------+---------+---------+--------+
- +------+---------+---------+--------+
The sales
table contains no
NULL
values, so all NULL
values in a ROLLUP
result represent
super-aggregate values. When the data set contains
NULL
values, ROLLUP
summaries may contain NULL
values not only in
super-aggregate rows, but also in regular grouped rows.
GROUPING()
enables these to be
distinguished. Suppose that table t1
contains
a simple data set with two grouping factors for a set of
quantity values, where NULL
indicates
something like “other” or “unknown”:
- +------+-------+----------+
- | name | size | quantity |
- +------+-------+----------+
- | ball | small | 10 |
- | ball | large | 20 |
- | hoop | small | 15 |
- | hoop | large | 5 |
- +------+-------+----------+
A simple ROLLUP
operation produces these
results, in which it is not so easy to distinguish
NULL
values in super-aggregate rows from
NULL
values in regular grouped rows:
- FROM t1
- +------+-------+----------+
- | name | size | quantity |
- +------+-------+----------+
- | ball | large | 20 |
- | ball | small | 10 |
- | hoop | large | 5 |
- | hoop | small | 15 |
- +------+-------+----------+
Using GROUPING()
to substitute
labels for the super-aggregate NULL
values
makes the result easier to interpret:
Other Considerations When using ROLLUP
The following discussion lists some behaviors specific to the
MySQL implementation of ROLLUP
.
Prior to MySQL 8.0.12, when you use ROLLUP
,
you cannot also use an ORDER BY
clause to
sort the results. In other words, ROLLUP
and ORDER BY
were mutually exclusive in
MySQL. However, you still have some control over sort order.
To work around the restriction that prevents using
ROLLUP
with ORDER BY
and
achieve a specific sort order of grouped results, generate the
grouped result set as a derived table and apply ORDER
BY
to it. For example:
- +------+--------+
- +------+--------+
- | 2001 | 3010 |
- | 2000 | 4525 |
- +------+--------+
As of MySQL 8.0.12, ORDER BY
and
ROLLUP
can be used together, which enables
the use of ORDER BY
and
GROUPING()
to achieve a
specific sort order of grouped results. For example:
- FROM sales
- +------+--------+
- +------+--------+
- | 2000 | 4525 |
- | 2001 | 3010 |
- +------+--------+
In both cases, the super-aggregate summary rows sort with the rows from which they are calculated, and their placement depends on sort order (at the end for ascending sort, at the beginning for descending sort).
LIMIT
can be used to restrict the number of
rows returned to the client. LIMIT
is
applied after ROLLUP
, so the limit applies
against the extra rows added by ROLLUP
. For
example:
- FROM sales
- +------+---------+------------+--------+
- +------+---------+------------+--------+
- | 2000 | Finland | Computer | 1500 |
- | 2000 | Finland | Phone | 100 |
- | 2000 | India | Calculator | 150 |
- | 2000 | India | Computer | 1200 |
- +------+---------+------------+--------+
Using LIMIT
with ROLLUP
may produce results that are more difficult to interpret,
because there is less context for understanding the
super-aggregate rows.
A MySQL extension permits a column that does not appear in the
GROUP BY
list to be named in the select
list. (For information about nonaggregated columns and
GROUP BY
, see
Section 12.20.3, “MySQL Handling of GROUP BY”.) In this case, the server
is free to choose any value from this nonaggregated column in
summary rows, and this includes the extra rows added by
WITH ROLLUP
. For example, in the following
query, country
is a nonaggregated column
that does not appear in the GROUP BY
list
and values chosen for this column are nondeterministic:
- FROM sales
- +------+---------+--------+
- +------+---------+--------+
- | 2000 | India | 4525 |
- | 2001 | USA | 3010 |
- +------+---------+--------+
This behavior is permitted when the
ONLY_FULL_GROUP_BY
SQL mode
is not enabled. If that mode is enabled, the server rejects
the query as illegal because country
is not
listed in the GROUP BY
clause. With
ONLY_FULL_GROUP_BY
enabled,
you can still execute the query by using the
ANY_VALUE()
function for
nondeterministic-value columns:
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-group-by-modifiers.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.