15.00 - GROUPING - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

GROUPING

Purpose  

Returns a value that indicates whether a specified column in the result row was excluded from the grouping set of a GROUP BY clause.

Syntax  

where:

 

Syntax element …

Specifies …

expression

A column in the result row that might have been excluded from a grouped query containing CUBE, ROLLUP, or GROUPING SET.

The argument must be an item of a GROUP BY clause.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes  

A null in the result row of a grouped query containing CUBE, ROLLUP, or GROUPING SET can mean one of the following:

  • The actual data for the column is null.
  • The extended grouping specification aggregated over the column and excluded it from the particular grouping. A null in this case really represents all values for this column.
  • Use GROUPING to distinguish between rows with nulls in actual data from rows with nulls generated from grouping sets.

    Result Type and Attributes

    The data type, format, and title for GROUPING(x) are as follows.

     

    Data Type

    Format

    Title

    INTEGER

    Default format of the INTEGER data type

    Grouping(x)

    For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Result Value

     

    IF the value of the specified column in the result row is …

    THEN GROUPING returns …

    a NULL generated when the extended grouping specification aggregated over the column and excluded it from the particular grouping

    1

    anything else

    0

    Example  

    Suppose you have the following data in the sales_view table.

     

    PID

    Cost

    Sale

    Margin

    State

    County

    City

    1

    38350

    50150

    11800

    CA

    Los Angeles

    Long Beach

    1

    63375

    82875

    19500

    CA

    San Diego

    San Diego

    1

    46800

    61200

    14400

    CA

    Los Angeles

    Avalon

    2

    40625

    53125

    12500

    CA

    Los Angeles

    Long Beach

    To look at sales summaries by county and by city, use the following SELECT statement:

       SELECT county, city, sum(margin) 
       FROM sale_view
       GROUP BY GROUPING SETS ((county),(city));

    The query reports the following data:

       County       City        Sum(margin)
       -----------  ----------  -----------
       Los Angeles  ?                 38700
       San Diego    ?                 19500
       ?            Long Beach        24300
       ?            San Diego         19500
       ?            Avalon            14400

    Notice that in this example, a null represents all values for a column because the column was excluded from the grouping set represented.

    To distinguish between rows with nulls in actual data from rows with nulls generated from grouping sets, use the GROUPING function:

       SELECT county, city, sum(margin), 
              GROUPING(county) AS County_Grouping, 
              GROUPING(city) AS City_Grouping
       FROM sale_view
       GROUP BY GROUPING SETS ((county),(city));

    The results are:

       County      City       Sum(margin) County_Grouping City_Grouping
       ----------- ---------- ----------- --------------- -------------
       Los Angeles ?                38700               0             1
       San Diego   ?                19500               0             1
       ?           Long Beach       24300               1             0
       ?           San Diego        19500               1             0
       ?           Avalon           14400               1             0
       

    You can also use GROUPING to replace the nulls that appear in a result row because the extended grouping specification aggregated over a column and excluded it from the particular grouping. For example:

       SELECT CASE 
                WHEN GROUPING(county) = 1
                THEN '-All Counties-'
                ELSE county
              END AS County,
              CASE 
                WHEN GROUPING(city) = 1
                THEN '-All Cities-'
                ELSE city
              END AS City,
              SUM(margin) 
       FROM sale_view
       GROUP BY GROUPING SETS (county,city);

    The query reports the following data:

       County          City          Sum(margin)
       --------------  ------------  -----------
       Los Angeles     -All Cities-        38700
       San Diego       -All Cities-        19500
       -All Counties-  Long Beach          24300
       -All Counties-  San Diego           19500
       -All Counties-  Avalon              14400

    Related Topics

    For more information on GROUP BY, GROUPING SETS, ROLLUP, and CUBE, see SQL Data Manipulation Language.