Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
refer select field alias in GROUP BY clause

- SELECT
- col1
- , col2
- , CASE
- WHEN col2 = 'a'
- THEN 1
- ELSE 2
- END AS my_case_1
- , SUM (col3 / col4) AS my_formula_1
- FROM
- table1 AS a
- WHERE
- my_formula_1 < 1
- GROUP BY
- col_a
- , col_b
- , my_case_1;
Comments
-
Not absolutely required but makes life a bit easier. I vote ein favour.
-
If the SELECT list item is a scalar subquery, this syntax could also let the optimiser know it can re-use the results in the corresponding GROUP BY list item rather than re-execute it (if it doesn't already), so there might be performance optimisations as well as the reduced maintenance overheard. I like it
-
If the SELECT list item is a scalar subquery, this syntax could also let the optimiser know it can re-use the results in the corresponding GROUP BY list item rather than re-execute it (if it doesn't already), so there might be performance optimisations as well as the reduced maintenance overheard. I like it
Hm... can't make my mind up about it.
There could be quite some ambiguity.
consider this example:
with testdata as (select trunc(sysdate,'YEAR')+level day from dual connect by level<100 ) select to_char(day,'DD') as Day, to_char(day,'Month') as Month from testdata group by day;
which day do you want to group by? Apart from using reserved words this just shows, that a column name can be repeated in the select expression. But the value did change considerably. If suddenly the column name is leading the group by this select would give "ORA-00979: not a GROUP BY expression" or some other error.
-
Hm... can't make my mind up about it.
There could be quite some ambiguity.
consider this example:
with testdata as (select trunc(sysdate,'YEAR')+level day from dual connect by level<100 ) select to_char(day,'DD') as Day, to_char(day,'Month') as Month from testdata group by day;
which day do you want to group by? Apart from using reserved words this just shows, that a column name can be repeated in the select expression. But the value did change considerably. If suddenly the column name is leading the group by this select would give "ORA-00979: not a GROUP BY expression" or some other error.
Surely this is the same issue with any field name?
I definitely would prefer aliases to be usable in GROUP BY clause. Much more readable and intuitive. Other versions of SQL allow it
-
Surely this is the same issue with any field name?
I definitely would prefer aliases to be usable in GROUP BY clause. Much more readable and intuitive. Other versions of SQL allow it
user1592128 wrote: Surely this is the same issue with any field name? I definitely would prefer aliases to be usable in GROUP BY clause. Much more readable and intuitive. Other versions of SQL allow it
No currently this is not an issue, because in the group by clause you can have input column names (e.g. field names), but not output column names (aliases from the select list).
If you allow aliases from the select list, how to solve this ambiquity? Would the select list alias always get priority? Other databases that allow such a syntax have sever issues if you add a column to an underlying table/view with the same column name as the select alias is, then this column would be used for grouping and you might end up with a syntax error (best case) or silently with totally different data (worst case).
The typical workaround in oracle is an inline view or with clause subquery. I do not see why that is too much effort.
btw: As far as other databases go: SQL Server does not allow it but PostgreSql and Teradata seem to allow it. Although PostgreSql does not allow it in the having clause.
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-GROUPBY
GROUP BY
will condense into a single row all selected rows that share the same values for the grouped expressions.expression
can be an input column name, or the name or ordinal number of an output column (SELECT
list item), or an arbitrary expression formed from input-column values. In case of ambiguity, aGROUP BY
name will be interpreted as an input-column name rather than an output column name. -
I don't really like this idea, even if I perfectly understand the motivation. But it will be rather confusing what is allowed and what is not. For instance:
SELECT a || b AS c, COUNT(*) AS d, COUNT(*) OVER () AS e
FROM t
GROUP BY
c, -- Allowed
d, -- Not possible
e -- Not possible
I think the problem would be solved much more thoroughly, if the inverse was possible: Aliasing grouping columns, such as:
SELECT c /* Grouping alias being used here */, COUNT(*) AS d, COUNT(*) OVER () AS e
FROM t
GROUP BY a || b AS c -- Alias here
HAVING c LIKE '%x%' -- Grouping alias being used here
It would have the same, desired effect of not needing to repeat oneself, but it would not introduce any ambiguities nor confusion about what's possible and what is not.
-
I backed this. Don't really understand the motivation of the ones who doesn't. It's all the same for any powerful tools - it's all up to you how to use it, either for good or you can shot your leg with it.
Yes, there's a workaround for this. Yes I understand if this ever be implemented it's not 100% safe, considering the above examples one can mess with it. But hell, there're so many other things in oracle you can mess with.
I'd even wanted to have an option to reference column aliases in the SELECT part like this:
SELECT a.col1 + a.col2 as my_sum,
my_sum * 10 as my_sum_times10
FROM sometable a
... -
user1592128 wrote: Surely this is the same issue with any field name? I definitely would prefer aliases to be usable in GROUP BY clause. Much more readable and intuitive. Other versions of SQL allow it
No currently this is not an issue, because in the group by clause you can have input column names (e.g. field names), but not output column names (aliases from the select list).
If you allow aliases from the select list, how to solve this ambiquity? Would the select list alias always get priority? Other databases that allow such a syntax have sever issues if you add a column to an underlying table/view with the same column name as the select alias is, then this column would be used for grouping and you might end up with a syntax error (best case) or silently with totally different data (worst case).
The typical workaround in oracle is an inline view or with clause subquery. I do not see why that is too much effort.
btw: As far as other databases go: SQL Server does not allow it but PostgreSql and Teradata seem to allow it. Although PostgreSql does not allow it in the having clause.
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-GROUPBY
GROUP BY
will condense into a single row all selected rows that share the same values for the grouped expressions.expression
can be an input column name, or the name or ordinal number of an output column (SELECT
list item), or an arbitrary expression formed from input-column values. In case of ambiguity, aGROUP BY
name will be interpreted as an input-column name rather than an output column name.I also would like see something like this implemented. The position number also sounds nice (so GROUP BY and ORDER BY match).
Ambiguities should raise ORA-00918 (which is not raised often enough anyway, maybe require unique column lists generally even if they aren't referenced). Referencing constructs that already group (COUNT/OVER) should also raise a (new) error (mirror of ORA-00979).
Same row alias resolution (like post below) for good measure.
Maybe this will/should mainly be used for rapid prototyping rather than production code (like ORDER BY 1, 2 or SELECT *).
-
-
GROUP BY alias|position comes in 23c.
(Standard safe harbour applies, what you see in the 23c beta release may or may not end up production etc)