Combining Window Functions, Teradata-Specific Functions, and GROUP BY

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

The following table provides the semantics of the allowable combinations of window functions, Teradata-specific functions, aggregate functions, and the GROUP BY clause.

    Combination   Semantics
Window Function Teradata-Specific Function Aggregate Function GROUP BY Clause  
X       A value is computed for each row.
  X     A value is computed for each row. The entire table constitutes a single group, or partition, over which the Teradata-specific function executes.
    X   One aggregate value is computed for the entire table.
X     X GROUP BY collapses all rows with the same value for the group-by columns into a single row, and a value is computed for each resulting row.
  X   X GROUP BY determines the partitions over which the Teradata-specific function executes. The clause does not collapse all rows with the same value for the group-by columns into a single row.
    X X An aggregation is performed for each group.
X X     Teradata-specific functions do not have partitions. The whole table is one partition.
X X   X GROUP BY determines partitions for Teradata-specific functions. GROUP BY does not collapse all rows with the same value for the group-by columns into a single row, and does not affect window function computation.
X   X X GROUP BY collapses all rows with the same value for the group-by columns into a single row. For window functions, a value is computed for each resulting row; for aggregate functions, an aggregation is performed for each group.