Обсуждение: Column Alias Not Allowed In Grouping Function
Suppose we have a table SALES with the following columns:
cust_name
sales_person_name
sale_amt
then the SQL command:
SELECT cust_name c , sales_person_name s , SUM(sale_amt) , GROUPING(c , s) grp
FROM SALES
GROUP BY CUBE(c,s)
ORDER BY grp;
will fail. But,
SELECT cust_name c , sales_person_name s , SUM(sale_amt) , GROUPING( cust_name , sales_person_name) grp
FROM SALES
GROUP BY CUBE(c,s)
ORDER BY grp;
will succeed.
Why is it illegal to use the column aliases in the GROUPING function?
Vivek Suraiya <vivek_suraiya@yahoo.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