Nesting Aggregates in Window Functions

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

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;