content_image/719992.jpg content_image/719993.jpg
• FORMAT( X , D ) 

Formats the number X  to a format like ’#,###,###.##’ , rounded to D  decimal places, and returns the result as a string. For details, see Section 12.8, “String Functions and Operators” . 

• GROUPING( expr  [, expr ] ...) 

For GROUP BY  queries that include a WITH ROLLUP  modifier, the ROLLUP  operation produces super- aggregate output rows where NULL  represents the set of all values. The GROUPING()  function enables you to distinguish NULL  values for super-aggregate rows from NULL  values in regular grouped rows. 

GROUPING()  is permitted in the select list, HAVING  clause, and (as of MySQL 8.0.12) ORDER BY clause. 

Each argument to GROUPING()  must be an expression that exactly matches an expression in the GROUP BY  clause. The expression cannot be a positional specifier. For each expression, GROUPING() produces 1 if the expression value in the current row is a NULL  representing a super-aggregate value. Otherwise, GROUPING()  produces 0, indicating that the expression value is a NULL  for a regular result row or is not NULL . 

Suppose that table t1  contains these rows, where NULL  indicates something like “other”  or “unknown” : 

mysql> SELECT * FROM t1; 

<img src='content_image/719992.jpg'>

A summary of the table without WITH ROLLUP  looks like this: 

mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size; 

<img src='content_image/719993.jpg'>

The result contains NULL  values, but those do not represent super-aggregate rows because the query does not include WITH ROLLUP . 

Adding WITH ROLLUP  produces super-aggregate summary rows containing additional NULL  values. However, without comparing this result to the previous one, it is not easy to see which NULL  values occur in super-aggregate rows and which occur in regular grouped rows: 

mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP; +------+-------+----------+ | name | size | quantity | 

