SQLearnAcademy
←Function reference
AggregateSQL standard

GROUPING

Shows whether a row is a ROLLUP or CUBE subtotal for a column: 1 — a subtotal, 0 — an ordinary group.

Syntax

GROUPING(<column>)
GROUPING(<column1>, <column2>, …)

ROLLUP adds subtotal rows to a report, where the grouping column becomes NULL. But NULL can also be a real value, so it can't tell a subtotal from a “no department” group. GROUPING gives 1 precisely for the subtotal row — that's what you use to label it “Total”.

Data for the examples

employees

namedepartmentsalarybonusphone
Emmabackend1200300+44 7700 900123
Liambackend1000NULLNULL
Oliviafrontend1350150+44 7700 900456
Noahfrontend950NULL+44 7700 900789
Sophiadesign1200200NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

A subtotal marked by GROUPING
SELECT department,
SUM(salary) AS payroll,
GROUPING(department) AS is_total
FROM employees
GROUP BY ROLLUP (department)
ORDER BY is_total, department;
departmentpayrollis_total
backend22000
design12000
frontend23000
NULL57001
A Total label instead of NULLAdvanced
SELECT CASE WHEN GROUPING(department) = 1 THEN 'Total' ELSE department END AS department,
SUM(salary) AS payroll
FROM employees
GROUP BY ROLLUP (department)
ORDER BY GROUPING(department), department;
departmentpayroll
backend2200
design1200
frontend2300
Total5700

Watch out

Without ROLLUP, CUBE or GROUPING SETS, all rows are ordinary, and GROUPING always gives 0.

The argument must be a column from GROUP BY: otherwise you get arguments to GROUPING must be grouping expressions of the associated query level.

For several columns the result is a bit mask: GROUPING(a, b) gives 0, 1, 2 or 3. It's simpler to call GROUPING separately for each column.

Related functions

SUMCOUNTCASE