Notes on grouping behavior

This topic describes some EQL grouping behaviors that you should be aware of.

Implicit nested aggregation

Nested aggregation is not allowed in EQL (that is, an aggregation function cannot be nested inside another aggregation function). The following are examples of explicit nested aggregation functions:
SELECT COUNT(SUM(sales)) AS totals GROUP // Invalid

SELECT COUNT(sales) AS totals, SUM(totals) AS totals2 GROUP // Invalid
However, EQL allows adding an implicit ARB. For example, the following two queries are equivalent:
SELECT sales AS totals GROUP // Valid implicit ARB

SELECT ARB(sales) AS sales GROUP // Valid explicit ARB
Therefore, the implicit ARB can result in implicit nested aggregation. For example, the following two queries are equivalent and will result in implicit nested aggregation:
SELECT sales AS totals, COUNT(totals) AS totals2 GROUP // Invalid

SELECT ARB(sales) AS totals, COUNT(totals) AS totals2 GROUP // Invalid
Note that setting the alias to be the same name as the selected attribute can make nested aggregation less obvious. The following two queries are equivalent and invalid:
SELECT sales AS sales, COUNT(sales) AS totals GROUP // Invalid

SELECT ARB(sales) AS sales, COUNT(sales) AS totals GROUP // Invalid
The solution is to use a different alias, as in these two queries, which are equivalent and valid:
SELECT sales AS sales1, COUNT(sales) AS totals GROUP // Valid

SELECT ARB(sales) AS sales1, COUNT(sales) AS totals GROUP // Valid

GROUPING and GROUPING_ID interaction with attribute source

Setting an alias to be the same as a selected attribute can change the attribute source. For example, in the following query, amount in stmt1_amount refers to stmt1.amount, while amount in stmt2_amount refers to stmt2.amount:
SELECT stmt1 AS SELECT amount AS amount;
SELECT stmt2 AS SELECT amount+1 AS stmt1_amount, amount+2 AS amount, amount+3 AS stmt2_amount FROM stmt1
This also applies when using the GROUPING and GROUPING_ID functions:
SELECT stmt1 AS SELECT amount AS amount;
SELECT GROUPING(amount) AS stmt1_amount, amount AS amount,
  GROUPING(amount) AS stmt2_amount, orders AS orders,
  FROM stmt1 
  GROUP BY CUBE(amount, orders)

Implicit selects

Implicit selects are added to the end of the select list. For example, the following two queries are equivalent:
SELECT COUNT(sales) AS cnt GROUP BY totals, price

SELECT COUNT(sales) AS cnt, totals AS totals, price AS price GROUP BY totals, price

This only affects constructs that have different pre-aggregate and post-aggregate behavior, such as the GROUPING function.