When I attempt to use a field alias in a group by clause, I get “ambiguous reference” errors. For example, from the tutorial, the following query works:
SELECT SUBSTR(purchases.purchasedAt, 0, 7) as month,
ROUND(SUM(product.unitPrice * items.count)/1000000, 3) revenueMillion
FROM purchases UNNEST purchases.lineItems AS items JOIN product ON KEYS items.product
GROUP BY SUBSTR(purchases.purchasedAt, 0, 7)
ORDER BY month
If I change the group by clause to
GROUP BY month
the query fails with an ambiguous reference error.
I have encountered this problem in a number of queries and I find it curious that the problem appears to be restricted to GROUP BY; there is no problem using an ORDER BY clause with an alias.
Is this intended behavior? If so why? What makes the invocation of an alias ambiguous in the GROUP BY clause?