16.20 - Nesting Aggregates in Window Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

Although you can nest aggregates in window functions, including the select list, HAVING, QUALIFY, and ORDER BY clauses, the HAVING clause can only contain aggregate function references because HAVING cannot contain nested syntax like RANK() OVER (ORDER BY SUM(x)).

Aggregate functions cannot be specified with Teradata-specific functions.

Example

The following query nests the SUM aggregate function within the RANK ordered analytical function in the select list:

   SELECT state, city, SUM(sale), 
   RANK() OVER (PARTITION BY state ORDER BY SUM(sale))
   FROM T1
   WHERE T1.cityID = T2.cityID
   GROUP BY state, city
   HAVING MAX(sale) > 10;