GROUPING
Purpose
Returns a value that indicates whether a specified column in the result row was excluded from the grouping set of a GROUP BY clause.
Syntax
where:
|
Syntax element … |
Specifies … |
|
expression |
A column in the result row that might have been excluded from a grouped query containing CUBE, ROLLUP, or GROUPING SET. The argument must be an item of a GROUP BY clause. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Usage Notes
A null in the result row of a grouped query containing CUBE, ROLLUP, or GROUPING SET can mean one of the following:
Use GROUPING to distinguish between rows with nulls in actual data from rows with nulls generated from grouping sets.
Result Type and Attributes
The data type, format, and title for GROUPING(x) are as follows.
|
Data Type |
Format |
Title |
|
INTEGER |
Default format of the INTEGER data type |
Grouping(x) |
For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.
Result Value
|
IF the value of the specified column in the result row is … |
THEN GROUPING returns … |
|
a NULL generated when the extended grouping specification aggregated over the column and excluded it from the particular grouping |
1 |
|
anything else |
0 |
Example
Suppose you have the following data in the sales_view table.
|
PID |
Cost |
Sale |
Margin |
State |
County |
City |
|
1 |
38350 |
50150 |
11800 |
CA |
Los Angeles |
Long Beach |
|
1 |
63375 |
82875 |
19500 |
CA |
San Diego |
San Diego |
|
1 |
46800 |
61200 |
14400 |
CA |
Los Angeles |
Avalon |
|
2 |
40625 |
53125 |
12500 |
CA |
Los Angeles |
Long Beach |
To look at sales summaries by county and by city, use the following SELECT statement:
SELECT county, city, sum(margin)
FROM sale_view
GROUP BY GROUPING SETS ((county),(city));
The query reports the following data:
County City Sum(margin)
----------- ---------- -----------
Los Angeles ? 38700
San Diego ? 19500
? Long Beach 24300
? San Diego 19500
? Avalon 14400
Notice that in this example, a null represents all values for a column because the column was excluded from the grouping set represented.
To distinguish between rows with nulls in actual data from rows with nulls generated from grouping sets, use the GROUPING function:
SELECT county, city, sum(margin),
GROUPING(county) AS County_Grouping,
GROUPING(city) AS City_Grouping
FROM sale_view
GROUP BY GROUPING SETS ((county),(city));
The results are:
County City Sum(margin) County_Grouping City_Grouping
----------- ---------- ----------- --------------- -------------
Los Angeles ? 38700 0 1
San Diego ? 19500 0 1
? Long Beach 24300 1 0
? San Diego 19500 1 0
? Avalon 14400 1 0
You can also use GROUPING to replace the nulls that appear in a result row because the extended grouping specification aggregated over a column and excluded it from the particular grouping. For example:
SELECT CASE
WHEN GROUPING(county) = 1
THEN '-All Counties-'
ELSE county
END AS County,
CASE
WHEN GROUPING(city) = 1
THEN '-All Cities-'
ELSE city
END AS City,
SUM(margin)
FROM sale_view
GROUP BY GROUPING SETS (county,city);
The query reports the following data:
County City Sum(margin)
-------------- ------------ -----------
Los Angeles -All Cities- 38700
San Diego -All Cities- 19500
-All Counties- Long Beach 24300
-All Counties- San Diego 19500
-All Counties- Avalon 14400
Related Topics
For more information on GROUP BY, GROUPING SETS, ROLLUP, and CUBE, see SQL Data Manipulation Language.