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

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.

   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;