- Removes the
group by allclause if it leads to an error that does not happen withoutgroup byclause
Some Systems allow the special group by all syntax as a shorthand for grouping on all select items that do not have an aggregate function.
SELECT customers.id, customers.name, count(orders.id)
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY ALLThis is equivalent to listing customers.id and customers.name in the group by clause explicitly.
The two short-hands provided by the standard (detection of functional dependencies and any_value) require a little more typing but provide a safety-net against accidental changes of the grouping key: If the example above is extended to also select the orders.placed column, it silently changes the grouping key—even if that was not intended.
Probably in PostgreSQL 19
Related
Normative References
The group by all syntax as shown above is not covered in ISO/IEC 9075-2:2023. The keyword all must not be mistaken for the <set quantifier>, which is also allowed in group by (T434, “GROUP BY DISTINCT”).
Probably in the Next Standard
This feature is probably in the next standard. However, as the standard doesn’t allow for expressions in the group by clause, group by all is non-conforming if items added to group by are not just column names.
As most systems do support expressions in group by, there is still a standardization gap that might lead to different results in different systems in a few corner cases, for example here: select a + COUNT(*) OVER().✓✗
