Emulated and Native Sets

PostgreSQL 9 (not 8.4), SQL Server, Oracle, SQL Anywhere and DB2 natively support grouping sets in their extended GROUP BY syntax. However, whenever there is no native support for a platform, or if your turned the Always Emulate Grouping Sets option on, we automatically emulate grouping sets with UNION ALL! This is how platforms like MySQL and MariaDB, or Sybase/SAP ASE, Firebird, SQLite, Ingres, SQL Server CE and Microsoft Access behave like they instantly generate grouping sets! All features below relate to both native and emulated grouping sets, and generated SQL query’s implementation details are totally transparent to your builder’s interface.

Grouping Functions

Create a new query using the emp table. Show builder rows Field, Group and Sort. Turn Page Size off. To find all total salaries per department and job, we Group By the fields DEPTNO and JOB, and choose Sum for SAL. Add two new first columns with Grouping Flag and Grouping Fields as aggregates. If you run the query, Grouping Flag will always be 0, and Grouping Fields will show the ordered list of column names you grouped by:

Grouping Flag and Grouping Fields are expressions based on the Grouping aggregate function exposed by your server. You can use it instead, for each GROUP BY field,  to get 0 when field is aggregated, or 1. But it is more convenient and friendly to see the 0, 1, 2… index of the combination and the list of field names used to group by in each combination.

There is also a shortcut: if you switch from “(none)” to one of the special grouping types (see below: Grouping Sets, Rollup or Cube) and you did not use yet any Grouping aggregate function in the Group bar, you’re ask to confirm you don’t want them automatically added for you. If yes, they will be added as they appear here, first column “flag” with Grouping Flag (sorted ascending), second column “flags” with Grouping Fields.

Special Group By

Locate now the Special GROUP BY toolbar button (the one with a cube icon) and select Grouping Sets. Sort by the Grouping Flag, then DEPTNO and JOB, to always get predictable and properly sorted rows. Run again your query:

First 9 rows (for Grouping Flag = 0) are the typical result of an aggregation per department AND job (as pointed by Grouping Fields too), when you have no special extension – just like in the previous image. Next three combinations extend the typical aggregation with grouping by DEPTNO only, by JOB only, or (the final single row) a grand total with no grouping by at all.

Alternatives to Grouping Sets: Cube and Rollup

Look at the GROUP BY clause of the generated SQL and check the Grouping Sets pairs: we generated for you ALL equivalents of combinations described before. Switch to Cube and you’ll find an equivalent simplified notation for ALL these combinations of grouping sets. While Rollup will exclude combination 2 from the result list.

Here are all specific GROUP BY clauses for this particular query and different Special GROUP BY choices:

GROUP BY GROUPING SETS( ("DEPTNO", "JOB"), ("JOB"), ("DEPTNO"), ( ) )

GROUP BY ROLLUP("DEPTNO", "JOB")

GROUP BY CUBE("DEPTNO", "JOB")

Group By Multiple Columns

Include another column to group by: MGR. You’ll have 8 groups now (0-7 in Grouping Flag), with all possible combinations between the three fields in Grouping Fields. As usual, you can drill-down to see All Aggregates combined for each result row. Let’s keep only the first and fourth combinations (with a Having clause). Also, click on Hide Nulls and Hide Consecutive Duplicates toolbar buttons, to get a different cleaner perspective on the results:

 

And this is it! This is how easy it was to learn about and create queries with Grouping Sets!