17.05 - Example: Semantically Identical Grouping Sets Specifications and Their Resolution - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following three queries return the same results set because they are semantically identical.

     SELECT y,m,r, SUM(u)
     FROM test
     GROUP BY CUBE(y,m), CUBE(r)
     ORDER BY 1,2,3;

     SELECT y,m,r,SUM(u)
     FROM test
     GROUP BY CUBE(y,m,r)
     ORDER BY 1,2,3;

     SELECT y,m,r,SUM(u)
     FROM test
     GROUP BY GROUPING SETS(y,()), GROUPING SETS(m,()),
              GROUPING SETS(r,())
     ORDER BY 1,2,3;

The following three queries return the same results set because they are semantically identical.

     SELECT y, m, r, s, SUM(u)
     FROM test
     GROUP BY ROLLUP(y,m),ROLLUP(r,s)
     ORDER BY 1,2,3,4;

     SELECT y, m, r, s, SUM(u)
     FROM test
     GROUP BY GROUPING SETS((y, m),(),y),ROLLUP(r,s)
     ORDER BY 1,2,3,4;

     SELECT y, m, r, s, SUM(u)
     FROM test
     GROUP BY GROUPING SETS((y,m),(),y),GROUPING SETS((),r,(r,s))
     ORDER BY 1,2,3,4;