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, 203🔥, 0💬
Popular Posts:
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
How To Get a List of All Tables with "sys.tables" View in SQL Server? If you want to see the table y...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...