Collections:
GROUPING() - Identifying Super-Aggregate Row
How to identify super-aggregate row in aggregation query result using the GROUPING() function?
✍: FYIcenter.com
GROUPING(exp) is a MySQL built-in function that returns 1 if the given exp is rolled up to all values in a super-aggregate row when the "GROUP BY exp WITH ROLLUP" clause is used. For example:
CREATE TABLE MyTable (name CHAR(8), size CHAR(8), quantity INTEGER); INSERT INTO MyTable VALUES ('ball', 'small', 10), ('ball', 'large', 20), ('ball', NULL, 5), ('hoop', 'small', 15), ('hoop', 'large', 5), ('hoop', NULL, 3); SELECT SUM(quantity), name, GROUPING(name), size, GROUPING(size) FROM MyTable GROUP BY name, size WITH ROLLUP ORDER BY name, size; -- +---------------+------+----------------+-------+----------------+ -- | SUM(quantity) | name | GROUPING(name) | size | GROUPING(size) | -- +---------------+------+----------------+-------+----------------+ -- | 58 | NULL | 1 | NULL | 1 | -- | 5 | ball | 0 | NULL | 0 | -- | 35 | ball | 0 | NULL | 1 | -- | 20 | ball | 0 | large | 0 | -- | 10 | ball | 0 | small | 0 | -- | 3 | hoop | 0 | NULL | 0 | -- | 23 | hoop | 0 | NULL | 1 | -- | 5 | hoop | 0 | large | 0 | -- | 15 | hoop | 0 | small | 0 | -- +---------------+------+----------------+-------+----------------+
Note that the SUM(quantity) value in the super-aggregate row is the super-sum of GROUP BY expressions being rolled up to all values.
Reference information of the GROUPING() function:
GROUPING(exp): int Returns 1, if the given exp is rolled up to all values in a super-aggregate row; otherwise 0. Arguments, return value and availability: exp: Required. The group by expression to be examined. int: Return value. 1, if exp is rolled up. Available since MySQL 4.0.
⇒ JSON_ARRAYAGG() - Building JSON Array in Group
⇐ GROUP_CONCAT() - Concatenating Items in Group
2023-12-19, 198🔥, 0💬
Popular Posts:
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
Where to find answers to frequently asked questions on Managing Security, Login and User in SQL Serv...
What is SQL Server Transact-SQL (T-SQL)? SQL Server Transact-SQL, also called T-SQL, is an extension...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...