GROUPING_ID
Description
Computes the level of grouping for rows in a GROUP BY query. The GROUPING_ID function returns an integer bitmap indicating which columns in the GROUP BY list are not aggregated for a given output row. It can be used in the SELECT list, HAVING, or ORDER BY clauses when a GROUP BY is specified.
Syntax
GROUPING_ID(<column_expression>[,...])
Parameters
| Parameters | Description |
|---|---|
<column_expression> | A column expression from the GROUP BY clause. |
Return Value
Returns a BIGINT value representing the grouping bitmap for the given columns.
Examples
Example A: Identify grouping levels
SELECT
department,
CASE
WHEN GROUPING_ID(department,level)=0THENlevel
WHEN GROUPING_ID(department,level)=1THEN CONCAT('Total: ', department)
WHEN GROUPING_ID(department,level)=3THEN'Total: Company'
ELSE'Unknown'
ENDAS`Job Title`,
COUNT(uid)AS`Employee Count`
FROM employee
GROUPBY ROLLUP(department,level)
ORDERBY GROUPING_ID(department,level)ASC;
Expected Output:
+--------------------+---------------------------+----------------+
| department | Job Title | Employee Count |
+--------------------+---------------------------+----------------+
| Board of Directors | Senior | 2 |
| Technology | Senior | 3 |
| Sales | Senior | 1 |
| Sales | Assistant | 2 |
| Sales | Trainee | 1 |
| Marketing | Senior | 1 |
| Marketing | Trainee | 2 |
| Marketing | Assistant | 1 |
| Board of Directors | Total: Board of Directors | 2 |
| Technology | Total: Technology | 3 |
| Sales | Total: Sales | 4 |
| Marketing | Total: Marketing | 4 |
| NULL | Total: Company | 13 |
+--------------------+---------------------------+----------------+
Example B: Filter result set using GROUPING_ID
SELECT
department,
CASE
WHEN GROUPING_ID(department,level)=0THENlevel
WHEN GROUPING_ID(department,level)=1THEN CONCAT('Total: ', department)
WHEN GROUPING_ID(department,level)=3THEN'Total: Company'
ELSE'Unknown'
ENDAS`Job Title`,
COUNT(uid)AS`Count`
FROM employee
GROUPBY ROLLUP(department,level)
HAVING`Job Title`='Senior';
Expected Output:
+--------------------+-----------+-------+
| department | Job Title | Count |
+--------------------+-----------+-------+
| Board of Directors | Senior | 2 |
| Technology | Senior | 3 |
| Sales | Senior | 1 |
| Marketing | Senior | 1 |
+--------------------+-----------+-------+