GROUPING()

Use the GROUPING() clause with ROLLUP to determine whether null values are the result of a ROLLUP operation or part of your dataset. GROUPING() returns 1 if the null value is a subtotal generated by a rollup. It returns 0 otherwise.

This example uses the GROUPING() function and CASE statements together to label the subtotal and grand total categories. The first case checks for a null value generated by the rollup in the Category field. If true, then the query labels the field "All Categories." Similarly, the second case checks whether a Sub-Category field is null. If true, the query labels the field "All Sub-Categories."

SELECT
(CASE WHEN GROUPING("Category") = 1 THEN 'All Categories' ELSE "Category" END) as "Category",
(CASE WHEN GROUPING("Sub_Category") = 1 THEN 'All Sub-Categories' ELSE "Sub_Category" END) as "SubCategory",
SUM("Sales") as "sum_sales"
FROM "Superstore"
GROUP BY ROLLUP("Category", "Sub_Category");