SQL GROUPING

What is the SQL GROUPING function?

Returns 1 when a column is aggregated by ROLLUP, CUBE, or GROUPING SETS and 0 when it participates in the current grouping level.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GROUPING: PostgreSQL (9.5+), SQL Server (2005+), Oracle (8i+), Snowflake, BigQuery, Redshift, DB2. Not supported in MySQL or SQLite.

SQL GROUPING Full Explanation

GROUPING is a scalar function defined in the SQL:1999 standard that works with advanced GROUP BY extensions such as ROLLUP, CUBE, and GROUPING SETS. When these operations produce subtotal or super-aggregate rows, columns not included in that specific grouping level are set to NULL. Because real data can also contain NULLs, GROUPING provides a reliable flag to distinguish system-generated NULLs from stored NULLs.The function evaluates each listed column or expression and returns an integer: 1 if the column was aggregated (not part of the grouping key) and 0 if it was present in the grouping key. GROUPING is deterministic, can appear in SELECT and ORDER BY clauses, and can be combined into a single bitmask via GROUPING_ID in some dialects.Caveats:- Only valid in the same SELECT list that contains a GROUP BY with ROLLUP, CUBE, or GROUPING SETS.- Returns 0 or 1; it is not an aggregate itself.- Cannot reference aliases defined in the same SELECT list.

SQL GROUPING Syntax

GROUPING ( column_or_expression );

SQL GROUPING Parameters

column_or_expression (any) - The column name or expression whose grouping status you want to check

Example Queries Using SQL GROUPING

-- Basic ROLLUP example
SELECT department,
       role,
       SUM(salary) AS total_salary,
       GROUPING(department) AS g_dept,
       GROUPING(role)       AS g_role
FROM   employees
GROUP BY ROLLUP (department, role);

-- Using GROUPING with GROUPING SETS
SELECT region,
       year,
       COUNT(*) AS orders,
       GROUPING(region) AS g_region,
       GROUPING(year)   AS g_year
FROM   sales
GROUP BY GROUPING SETS ((region, year), (region), ());

Expected Output Using SQL GROUPING

Each result row includes two additional columns (g_dept/g_role or g_region/g_year) set to 0 where the column participates in that level and 1 where it is aggregated, allowing you to identify subtotals and grand totals

Use Cases with SQL GROUPING

Differentiate artificial NULLs from real NULLs in subtotal rows
Label or filter subtotal rows when exporting data
Build custom rollup reports where layout depends on grouping level
Generate hierarchical totals in BI tools while preserving data quality

Common Mistakes with SQL GROUPING

Using IS NULL instead of GROUPING to detect subtotal NULLs
Omitting GROUPING when needed in ORDER BY, leading to wrong sort order
Expecting GROUPING to work without ROLLUP, CUBE, or GROUPING SETS
Referencing a SELECT alias inside the GROUPING function

First Introduced In

SQL:1999

Frequently Asked Questions

What does GROUPING do?

It flags whether each column in the SELECT list is aggregated (1) or part of the current grouping level (0) when you use ROLLUP, CUBE, or GROUPING SETS.

How is GROUPING different from GROUPING_ID?

GROUPING evaluates one column at a time. GROUPING_ID combines multiple GROUPING results into a single bitmask, letting you rank grouping levels more easily.

Can I filter subtotal rows using GROUPING?

Yes. Add `HAVING GROUPING(column) = 1` or similar logic to keep or exclude subtotal rows.

Does MySQL support GROUPING?

No. MySQL currently lacks native support for the GROUPING function, though you can emulate some behavior with conditional aggregation.