Advanced GROUPING Methods in Teradata SQL: GROUP BY GROUPING SETS, ROLLUP, and CUBE

Teradata SQL and Advanced GROUPING Functions

The advanced GROUPING functions will be demonstrated through the following example. The foundation of this demonstration is a table consisting of the flight count for each aircraft and date:

CREATE MULTISET TABLE Flights
(
   PLANE AS BIGINT NOT NULL,
   FLIGHTDATE AS DATE NOT NULL,
   NR_FLIGHTS INTEGER NOT NULL
) PRIMARY INDEX (PLANE);

Below you can see our test data:

Teradata SQL – GROUP BY GROUPING SETS

Data can be grouped on a singular dimension without ROLLUP or on multiple dimensions using CUBE. Consider this illustration:

SELECT 
    PLANE, 
    EXTRACT(MONTH FROM FLIGHTDATE) AS TheMonth,  
    EXTRACT(YEAR FROM FLIGHTDATE) AS TheYear, 
    SUM(NR_FLIGHTS) AS NR_FLIGHTS
FROM 
    FLIGHTS
GROUP BY GROUPING SETS (PLANE,TheMonth,TheYear)
ORDER BY 1 DESC,2 DESC,3 DESC;

GROUP BY GROUPING SETS determines the number of flights for each plane, monthly and annually.

Details about GROUP BY GROUPING SET

• All columns of GROUPING sets must reference column names. The referencing cannot be done using the position as it is possible in simple GROUP BY (GROUP BY 1,2).
• Each GROUPING SET may contain a maximum of 190 columns.
• Large objects cannot be used in a GROUPING SET (CLOB, BLOB). They must first be cast into another data type. CLOB can be cast to CHAR or VARCHAR. BLOB can be cast to BYTE or VARBYTE.

Teradata SQL – GROUP BY ROLLUP

Categorizes data along a singular dimension with multiple levels of detail. An illustration is presented below:

SELECT  
    PLANE, 
    EXTRACT(MONTH FROM FLIGHTDATE) AS TheMonth, 
    EXTRACT(YEAR FROM FLIGHTDATE) AS TheYear, 
    SUM(NR_FLIGHTS) AS NR_FLIGHTS 
 FROM 
    FLIGHTS
GROUP BY ROLLUP (Plane,TheMonth,TheYear) 
ORDER BY 1 DESC,2 DESC,3 DESC;

GROUP BY ROLLUP calculates the number of flights for each plane, each distinct month, each month per year, and the total.

Details about GROUP BY ROLLUP

• Several GROUPING SETS can be defined in one SQL. In our example, we use only one (Plane, TheMonth, TheYear)
• Large objects cannot be used in a GROUPING SET (CLOB, BLOB). They must first be cast into another data type. CLOB can be cast to CHAR or VARCHAR. BLOB can be cast to BYTE or VARBYTE.
• All columns of GROUPING sets must reference column names. The referencing cannot be done using the position as in simple GROUP BY (GROUP BY 1,2).
• GROUP BY ROLLUP on a single column corresponds to a simple GROUP BY statement
• Each GROUPING SET may contain a maximum of 190 columns.

Teradata SQL – GROUP BY CUBE

Organizes data across multiple dimensions. For instance:

SELECT 
    PLANE, 
    EXTRACT(MONTH FROM FLIGHTDATE) AS TheMonth, 
    EXTRACT(YEAR FROM FLIGHTDATE) AS TheYear,
    SUM(NR_FLIGHTS) AS NR_FLIGHTS 
FROM 
    FLIGHTS
GROUP BY CUBE (Plane,TheMonth,TheYear)
ORDER BY 1 DESC,2 DESC,3 DESC;

Details about GROUP BY CUBE

• Several GROUPING SETS can be defined in one Teradata SQL. In our example, we use only one (Plane, TheMonth, TheYear)
• All columns of GROUPING sets must reference column names. The referencing cannot be done using the position as in simple GROUP BY (GROUP BY 1,3).
• Large objects cannot be used in a GROUPING SET (CLOB, BLOB). They must first be cast into another data type. CLOB can be cast to CHAR or VARCHAR. BLOB can be cast to BYTE or VARBYTE.
• Each GROUPING SET may contain a maximum of 8 columns

Teradata’s advanced grouping options facilitate multi-level grouping, resulting in improved performance.