versioned_docs/version-3.x/sql-manual/sql-functions/aggregate-functions/grouping.md

---
{
    "title": "GROUPING",
    "language": "en",
    "description": "Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 if the column is aggregated (i.e.,"
}
---

## Description

Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 if the column is aggregated (i.e., coming from a summary row generated by ROLLUP, CUBE, or GROUPING SETS) and 0 if it is not aggregated. This function is useful for distinguishing the NULL values produced by these operations from actual NULL values in the data.

## Syntax

```sql
GROUPING( <column_expression> )
```

## Parameters

| Parameter             | Description                                                                  |
|-----------------------|------------------------------------------------------------------------------|
| `<column_expression>` | A column or an expression that appears in the GROUP BY clause.               |

## Return Value

Returns a BIGINT value. The function returns 1 if the specified column expression is aggregated and 0 if it is not.

## Examples

The following example groups the `camp` column and aggregates the count of `occupation`. The GROUPING function is applied to the `camp` column to differentiate between the summary row added by the ROLLUP operation and actual NULL values in the data.

```sql
CREATE TABLE `roles` (
  role_id       INT,
  occupation    VARCHAR(32),
  camp          VARCHAR(32),
  register_time DATE
)
UNIQUE KEY(role_id)
DISTRIBUTED BY HASH(role_id) BUCKETS 1
PROPERTIES (
  "replication_allocation" = "tag.location.default: 1"
);

INSERT INTO `roles` VALUES
(0, 'who am I', NULL, NULL),
(1, 'mage', 'alliance', '2018-12-03 16:11:28'),
(2, 'paladin', 'alliance', '2018-11-30 16:11:28'),
(3, 'rogue', 'horde', '2018-12-01 16:11:28'),
(4, 'priest', 'alliance', '2018-12-02 16:11:28'),
(5, 'shaman', 'horde', NULL),
(6, 'warrior', 'alliance', NULL),
(7, 'warlock', 'horde', '2018-12-04 16:11:28'),
(8, 'hunter', 'horde', NULL);

SELECT 
  camp, 
  COUNT(occupation) AS occ_cnt,
  GROUPING(camp) AS grouping
FROM
  `roles`
GROUP BY
  ROLLUP(camp);
```

The result set shows two NULL values under the `camp` column. The first NULL, with a grouping value of 1, is the summary row added by the ROLLUP operation representing the aggregate of all `camp` groups. The second NULL, with a grouping value of 0, represents an actual NULL value from the data.

```text
+----------+---------+----------+
| camp     | occ_cnt | grouping |
+----------+---------+----------+
| NULL     |       9 |        1 |
| NULL     |       1 |        0 |
| alliance |       4 |        0 |
| horde    |       4 |        0 |
+----------+---------+----------+
```