Docs

ByteHouse Cloud Data Warehouse Version

Function reference

GROUPING function

This article will Intro the usage of the GROUPING function and the GROUPING SETS modifier, and will also Intro the Core differences and use cases between the GROUPING SETS and CUBE modifiers and the basic GROUP BY clause.

GROUPING

ROLLUP and CUBE are modifiers for GROUP BY, both used to Calculate Data Subtotal:

ROLLUP takes an ordered list of columns (such as (day, month, year)), calculates Subtotal at each aggregate level in sequence, and finally returns the Total result;
CUBE calculates Subtotal for all possible combinations of the specified columns.

The GROUPING function is used to distinguish between two types of rows in the results returned by ROLLUP or CUBE: one is a higher-level aggregate row (superaggregate, that is, Subtotal row), and the other is a normal group row that the GROUP BY itself would return when ROLLUP/CUBE modifiers are not used.
The GROUPING function takes multiple columns as Param and returns a bitmask Value. The meaning of the return value is as follows:

1: Indicates that this row is a Subtotal row returned by ROLLUP or CUBE modified GROUP BY;
0: Indicates that this row is not a Subtotal row returned by ROLLUP or CUBE modified GROUP BY (that is, a normal group row).

GROUPING SETS

By default, the CUBE modifier calculates Subtotal for all possible combinations of all columns provided; while GROUPING SETS allows you to directly specify the exact column combinations to be calculated, making it more flexible to use. Typical application scenes for the ROLLUP, CUBE, and GROUPING SETS modifiers are hierarchical Data analysis.
The example in this article is based on a table that records the Linux distributions and their corresponding version information installed in two data centers. By splitting and viewing these data by the dimensions of "distribution", "version", and "data center location", it can provide effective reference value for business analysis.

Example data

This article will use the following table as an example. The sample commands are as follows:
Create table:

CREATE DATABASE bh_demo; 

CREATE TABLE bh_demo.servers ( 
    datacenter VARCHAR(255), 
    distro VARCHAR(255) NOT NULL, 
    version VARCHAR(50) NOT NULL, 
    quantity INT 
) 
ENGINE = CnchMergeTree() 
ORDER BY (datacenter, distro, version);

Insert data:

INSERT INTO bh_demo.servers(datacenter, distro, version, quantity) 
VALUES ('Schenectady', 'Arch','2022.08.05',50), 
       ('Westport', 'Arch','2022.08.05',40), 
       ('Schenectady','Arch','2021.09.01',30), 
       ('Westport', 'Arch','2021.09.01',20), 
       ('Schenectady','Arch','2020.05.01',10), 
       ('Westport', 'Arch','2020.05.01',5), 
       ('Schenectady','RHEL','9',60), 
       ('Westport','RHEL','9',70), 
       ('Westport','RHEL','7',80), 
       ('Schenectady','RHEL','7',80)

Query data:

SELECT 
    * 
FROM 
    bh_demo.servers;

Query results:

┌─datacenter──┬─distro─┬─version────┬─quantity─┐ 
│ Schenectady │ Arch   │ 2020.05.01 │       10 │ 
│ Schenectady │ Arch   │ 2021.09.01 │       30 │ 
│ Schenectady │ Arch   │ 2022.08.05 │       50 │ 
│ Schenectady │ RHEL   │ 7          │       80 │ 
│ Schenectady │ RHEL   │ 9          │       60 │ 
│ Westport    │ Arch   │ 2020.05.01 │        5 │ 
│ Westport    │ Arch   │ 2021.09.01 │       20 │ 
│ Westport    │ Arch   │ 2022.08.05 │       40 │ 
│ Westport    │ RHEL   │ 7          │       80 │ 
│ Westport    │ RHEL   │ 9          │       70 │ 
└─────────────┴────────┴────────────┴──────────┘ 

10 rows of data. Time: 0.359 seconds.

Simple query

The following queries use the basic GROUP BY clause to count the number of servers by different dimensions, clarifying the distribution of quantity under each dimension:

Count by the combined dimension of "data center + distribution"
Count the total number of servers for each Linux distribution (distro) in each data center (datacenter).
Run the query:

SELECT 
    datacenter, 
    distro, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    datacenter, 
    distro;

Query results:

┌─datacenter──┬─distro─┬─qty─┐ 
│ Schenectady │ RHEL   │ 140 │ 
│ Westport    │ Arch   │  65 │ 
│ Schenectady │ Arch   │  90 │ 
│ Westport    │ RHEL   │ 150 │ 
└─────────────┴────────┴─────┘ 

4 rows returned. Time: 0.060 seconds.

Statistics by single "Data Center" dimension
Count the total number of servers for each data center (datacenter), regardless of distro.
Run the query:

SELECT 
    datacenter, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    datacenter;

Query results:

┌─datacenter──┬─qty─┐ 
│ Westport    │ 215 │ 
│ Schenectady │ 230 │ 
└─────────────┴─────┘ 

Returns 2 rows. Time: 0.051 seconds.

Statistics by single "distro" dimension
Count the total number of servers for each Linux distro, regardless of data center.
Run the query:

SELECT 
    distro, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    distro;

Query results:

┌─distro─┬─qty─┐ 
│ Arch   │ 155 │ 
│ RHEL   │ 290 │ 
└────────┴─────┘ 

Returns 2 rows. Time: 0.037 seconds.

Total statistics (no grouping dimension)
Count the total number of all servers (regardless of data center and distro).
Run the query:

SELECT 
    SUM(quantity) qty 
FROM 
    bh_demo.servers;

Query results:

┌─qty─┐ 
│ 445 │ 
└─────┘ 

Returns 1 row. Time: 0.030 seconds.

Differences between GROUP BY and GROUPING SETS queries

The following examples compare two methods for implementing multi-dimensional aggregate statistics:

The first uses basic GROUP BY combined with UNION ALL to concatenate subqueries.
The second uses GROUPING SETS to achieve this in a single statement.

The core purpose of the following examples is to obtain, in one operation, the server quantity statistics for the four dimensions: "data center + distro", "data center", "distro", and "total".

Query using GROUP BY
Run the query:

SELECT 
    datacenter, 
    distro, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    datacenter, 
    distro 
UNION ALL 
SELECT 
    datacenter, 
    null, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    datacenter 
UNION ALL 
SELECT 
    null, 
    distro, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    distro 
UNION ALL 
SELECT 
    null, 
    null, 
    SUM(quantity) qty 
FROM 
    bh_demo.servers;

Query results:

┌─datacenter─┬─distro─┬─qty─┐ 
│ ᴺᵁᴸᴸ       │ ᴺᵁᴸᴸ   │ 445 │ 
└────────────┴────────┴─────┘ 
┌─datacenter──┬─distro─┬─qty─┐ 
│ Westport    │ ᴺᵁᴸᴸ   │ 215 │ 
│ Schenectady │ ᴺᵁᴸᴸ   │ 230 │ 
└─────────────┴────────┴─────┘ 
┌─datacenter──┬─distro─┬─qty─┐ 
│ Schenectady │ RHEL   │ 140 │ 
│ Westport    │ Arch   │  65 │ 
│ Schenectady │ Arch   │  90 │ 
│ Westport    │ RHEL   │ 150 │ 
└─────────────┴────────┴─────┘ 
┌─datacenter─┬─distro─┬─qty─┐ 
│ ᴺᵁᴸᴸ       │ Arch   │ 155 │ 
│ ᴺᵁᴸᴸ       │ RHEL   │ 290 │ 
└────────────┴────────┴─────┘ 

Returns 9 rows. Time: 0.066 seconds.

Query using GROUPING SETS
Run the query:

SELECT 
    datacenter, 
    distro, 
    SUM (quantity) qty 
FROM 
    bh_demo.servers 
GROUP BY 
    GROUPING SETS( 
        (datacenter,distro), 
        (datacenter), 
        (distro), 
        () 
    );

Query results:

┌─datacenter──┬─distro─┬─qty─┐ 
│ Schenectady │ RHEL   │ 140 │ 
│ Westport    │ Arch   │  65 │ 
│ Schenectady │ Arch   │  90 │ 
│ Westport    │ RHEL   │ 150 │ 
└─────────────┴────────┴─────┘ 
┌─datacenter──┬─distro─┬─qty─┐ 
│ Westport    │        │ 215 │ 
│ Schenectady │        │ 230 │ 
└─────────────┴────────┴─────┘ 
┌─datacenter─┬─distro─┬─qty─┐ 
│            │        │ 445 │ 
└────────────┴────────┴─────┘ 
┌─datacenter─┬─distro─┬─qty─┐ 
│            │ Arch   │ 155 │ 
│            │ RHEL   │ 290 │ 
└────────────┴────────┴─────┘ 

Returns 9 rows. Time: 0.040 seconds.

Differences between CUBE and GROUPING SETS queries

CUBE will generate all possible combinations of dimensions for the specified columns (including invalid or meaningless combinations). For example, in the following CUBE query, the CUBE(datacenter, distro, version) modifier generates a column combination hierarchy that may not meet actual analysis requirements. Because comparing versions across different distros (Arch and RHEL) is not meaningful, as their release periods and naming rules differ, direct comparison lacks reference value.
In contrast, GROUPING SETS supports customizing only the required valid combinations of dimensions, which better fits business needs. For example, in the following example, GROUPING SETS groups distro (distro) and version (version) together, ensuring that version comparisons are always based on the same distro dimension, making the analysis logic more reasonable.

Query using CUBE
Run the query:

SELECT 
   datacenter, 
   distro, 
   version, 
   SUM(quantity) 
FROM 
   bh_demo.servers 
GROUP BY 
   CUBE(datacenter,distro,version) 
ORDER BY 
   datacenter, 
   distro;

Query results:

┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ 
│             │        │ 7          │           160 │ 
│             │        │ 2020.05.01 │            15 │ 
│             │        │ 2021.09.01 │            50 │ 
│             │        │ 2022.08.05 │            90 │ 
│             │        │ 9          │           130 │ 
│             │        │            │           445 │ 
│             │ Arch   │ 2021.09.01 │            50 │ 
│             │ Arch   │ 2022.08.05 │            90 │ 
│             │ Arch   │ 2020.05.01 │            15 │ 
│             │ Arch   │            │           155 │ 
│             │ RHEL   │ 9          │           130 │ 
│             │ RHEL   │ 7          │           160 │ 
│             │ RHEL   │            │           290 │ 
│ Schenectady │        │ 9          │            60 │ 
│ Schenectady │        │ 2021.09.01 │            30 │ 
│ Schenectady │        │ 7          │            80 │ 
│ Schenectady │        │ 2022.08.05 │            50 │ 
│ Schenectady │        │ 2020.05.01 │            10 │ 
│ Schenectady │        │            │           230 │ 
│ Schenectady │ Arch   │ 2022.08.05 │            50 │ 
│ Schenectady │ Arch   │ 2021.09.01 │            30 │ 
│ Schenectady │ Arch   │ 2020.05.01 │            10 │ 
│ Schenectady │ Arch   │            │            90 │ 
│ Schenectady │ RHEL   │ 7          │            80 │ 
│ Schenectady │ RHEL   │ 9          │            60 │ 
│ Schenectady │ RHEL   │            │           140 │ 
│ Westport    │        │ 9          │            70 │ 
│ Westport    │        │ 2020.05.01 │             5 │ 
│ Westport    │        │ 2022.08.05 │            40 │ 
│ Westport    │        │ 7          │            80 │ 
│ Westport    │        │ 2021.09.01 │            20 │ 
│ Westport    │        │            │           215 │ 
│ Westport    │ Arch   │ 2020.05.01 │             5 │ 
│ Westport    │ Arch   │ 2021.09.01 │            20 │ 
│ Westport    │ Arch   │ 2022.08.05 │            40 │ 
│ Westport    │ Arch   │            │            65 │ 
│ Westport    │ RHEL   │ 9          │            70 │ 
│ Westport    │ RHEL   │ 7          │            80 │ 
│ Westport    │ RHEL   │            │           150 │ 
└─────────────┴────────┴────────────┴───────────────┘ 

Returns 39 rows. Time: 0.051 seconds.

warning

If the version is not associated with the distribution dimension, that is, the version (distribution version) split separately in the example above, then this grouping logic lacks practical analytical significance. If the analysis is focused on the kernel version, this combination logic is more reasonable, because the kernel version is universal across distributions and can be associated with any distribution.
In this scenario, selecting GROUPING SETS for group queries (as shown in the following example) better meets actual analytical needs.

Use GROUPING SETS to query
Run the query:

SELECT 
    datacenter, 
    distro, 
    version, 
    SUM(quantity) 
FROM bh_demo.servers 
GROUP BY 
    GROUPING SETS ( 
        (datacenter, distro, version), 
        (datacenter, distro));

Query results:

┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ 
│ Westport    │ RHEL   │ 9          │            70 │ 
│ Schenectady │ Arch   │ 2022.08.05 │            50 │ 
│ Schenectady │ Arch   │ 2021.09.01 │            30 │ 
│ Schenectady │ RHEL   │ 7          │            80 │ 
│ Westport    │ Arch   │ 2020.05.01 │             5 │ 
│ Westport    │ RHEL   │ 7          │            80 │ 
│ Westport    │ Arch   │ 2021.09.01 │            20 │ 
│ Westport    │ Arch   │ 2022.08.05 │            40 │ 
│ Schenectady │ RHEL   │ 9          │            60 │ 
│ Schenectady │ Arch   │ 2020.05.01 │            10 │ 
└─────────────┴────────┴────────────┴───────────────┘ 
┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ 
│ Schenectady │ RHEL   │         │           140 │ 
│ Westport    │ Arch   │         │            65 │ 
│ Schenectady │ Arch   │         │            90 │ 
│ Westport    │ RHEL   │         │           150 │ 
└─────────────┴────────┴─────────┴───────────────┘ 

Returns 14 rows. Time: 0.041 seconds

Last updated: 2026.03.31 16:14:29

ByteHouse Cloud Data Warehouse Version

GROUPING #

GROUPING SETS #

Example data

Simple query

Differences between GROUP BY and GROUPING SETS queries

Differences between CUBE and GROUPING SETS queries

GROUPING

GROUPING SETS