MySQL 8.0: GROUPING function

Starting with MySQL 8.0.1, the server supports the SQL GROUPING function.  The GROUPING function is used to distinguish between a NULL representing the set of all values in a super-aggregate row  (produced by a ROLLUP operation) from a NULL in a regular row.

Introduction

MySQL server has supported GROUP BY extension ROLLUP for sometime now.  Here is an example of how to use ROLLUP with GROUP BY.

As we see in the above result,  NULL’s are added by the ROLLUP modifier for every super aggregate row.

Now let us add NULL’s into the table data:

We have the following result when we query the data with ROLLUP after the addition of NULL’s into table data.

As we can see in the above example, it is now difficult to distinguish whether a  NULL is representing a regular grouped value or a super-aggregate value.

What is new in MySQL-8.0.1

The GROUPING function can be used in the above example to differentiate NULLs produced by ROLLUP from NULLs from the grouped data.  GROUPING function for a column returns a value of 1 when the NULL generated for that column is a result of ROLLUP operation. Else it returns a value of 0.

As we see in the above example,  GROUPING(b) returns a value of 1 only for those rows which have NULLs produced by a ROLLUP operation.

Another way of using the GROUPING function is by passing multiple columns as arguments to a single GROUPING function. The result of the GROUPING function would then be an integer bit mask having 1’s for the arguments which have GROUPING(argument) as 1.

For example:

As seen here, if GROUPING (a,b) returns 3, it means that NULL in column “a” and NULL in column “b” for that row is produce by a ROLLUP operation. If result is 1, NULL in column “b” alone is a result of ROLLUP operation.

Other uses of GROUPING function

We can specify GROUPING function in a select list or in a having condition. When specified in having condition, we can use this function to retrieve only super-aggregate rows or only aggregate rows like in the example below.

We can also use GROUPING function to differentiate super aggregates from aggregates in a nice way as shown below:

Those were some of the uses of newly added GROUPING function in MySQL server.

Conclusion

We would like to thank  Zhe Dong for his contribution, which we used as the basis of this feature addition.  Please try out GROUPING, and let us know your feedback 🙂

 

About Chaithra Gopalareddy

Chaithra Gopalaredy has been working with MySQL database team since 2011. She is currently employed by Oracle, based in India, Bangalore. She is Principal Software Developer for the Optimizer Team of the MySQL database. She has a background in the database industry, working for Alcatel Lucent's DataBlitz main memory database in past.

5 thoughts on “MySQL 8.0: GROUPING function

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.