Skip to main content
Skip to main content
Edit this page

GROUPING

GROUPING​

ROLLUP and CUBE are modifiers to GROUP BY. Both of these calculate subtotals. ROLLUP takes an ordered list of columns, for example (day, month, year), and calculates subtotals at each level of the aggregation and then a grand total. CUBE calculates subtotals across all possible combinations of the columns specified. GROUPING identifies which rows returned by ROLLUP or CUBE are superaggregates, and which are rows that would be returned by an unmodified GROUP BY.

The GROUPING function takes multiple columns as an argument, and returns a bitmask.

  • 1 indicates that a row returned by a ROLLUP or CUBE modifier to GROUP BY is a subtotal
  • 0 indicates that a row returned by a ROLLUP or CUBE is a row that is not a subtotal

GROUPING SETS​

By default, the CUBE modifier calculates subtotals for all possible combinations of the columns passed to CUBE. GROUPING SETS allows you to specify the specific combinations to calculate.

Analyzing hierarchical data is a good use case for ROLLUP, CUBE, and GROUPING SETS modifiers. The sample here is a table containing data about what Linux distribution, and the version of that distribution is installed across two datacenters. It may be valuable to look at the data by distribution, version, and location.

Load sample data​

Simple queries​

Get the count of servers in each data center by distribution:

Comparing multiple GROUP BY statements with GROUPING SETS​

Breaking down the data without CUBE, ROLLUP, or GROUPING SETS:

Getting the same information using GROUPING SETS:

Comparing CUBE with GROUPING SETS​

The CUBE in the next query, CUBE(datacenter,distro,version) provides a hierarchy that may not make sense. It does not make sense to look at Version across the two distributions (as Arch and RHEL do not have the same release cycle or version naming standards). The GROUPING SETS example following this one is more appropriate as it groups distro and version in the same set.

Note

Version in the above example may not make sense when it is not associated with a distro, if we were tracking the kernel version it might make sense because the kernel version can be associated with either distro. Using GROUPING SETS, as in the next example, may be a better choice.