Re: Column Alias Not Allowed In Grouping Function

Vivek Suraiya <vivek_suraiya(at)yahoo(dot)com> writes:
> SELECT cust_name c , sales_person_name s , SUM(sale_amt) , GROUPING(c , s) grpFROM SALESGROUP BY CUBE(c,s)ORDER BY grp;
> will fail.

Yup.

> Why is it illegal to use the column aliases in the GROUPING function?

Because the inputs to GROUPING are (notionally, at least) computed before
the SELECT targetlist is.

I'm aware that we allow things like "SELECT x ... GROUP BY 1", but
that's a holdover from SQL92 that's not even legal per SQL99 and
later standards. We're not going to try to extend that into
modern constructs like GROUPING(); it would create a morass of
ambiguity.

regards, tom lane