E051-02, “GROUP BY clause”

1. ⚡Incompatible access to select names possible

Extensions

The group by clause has a number of widely available extensions. First of all it must be noted that a standard SQL group by clause accepts column names only—expressions are not allowed. All tests systems accept expressions nevertheless ①. In that case it is handy if the group by clause can also refer to columns declared in the corresponding select clause ②. This extension is also widely available but might come with a danger:

SELECT b AS a
  FROM (VALUES (1, 1), (1, 2)) t(a,b)
 GROUP BY a

The column a defined in the select clause is not visible to the group by clause in standard SQL. Therefore, the column name a in the group by clause refers to the column a of the table t so that the query produces a single row. Some of the systems that make the columns of the select clause visible in group by give precedence to select list columns over from clause columns. Such systems produce an incompatible result with two rows.

An entirely different option to refer to select columns from the group by clause is by using a unsigned integer that is interpreted as the 1-based column number of the select clause ③. While this does not introduce dangerous ambiguity,0 there is the risk that changing the select clause also changes the meaning of the group by clause in an unintended way. The bigger the query is—actually, the further the select and group by clauses are apart—, the higher the risk. The same risk applies to the group by all variant ④, which is probably part of the next SQL standard.

1. Only if the complete select entry equals the group by entry. I.e. not:
   SELECT a + b FROM ... GROUP BY a, b
2. ⚡Select clause names take precedence over from clause columns
3. Cannot mix aggregates with non-aggregated columns: a + count(*)
4. Removes the group by all clause if it leads to an error that does not happen without group by clause

Related

Standard Features

• E051-04, “GROUP BY can contain columns not in <select list>”

• E051-06, “HAVING clause”

• T301, “Functional dependencies”

• The ANY_VALUE(…) Aggregate Function: New in SQL:2023 — and a better feature from 1999

Non-standard Features

• Group by All: Generate group by from select

Normative References

E051-02, “GROUP BY clause” is a mandatory feature in ISO/IEC 9075-2:2023. The functionality was required in ANSI X3.135-1989 already.