Waiting for SQL:202y: Group by All

Summary

Making GROUP BY a bit easier to use is in my experience among the top three requested features in SQL. Like, if you do CREATE TABLE t1 (a int, b int, ...); SELECT a, avg(b) FROM t1 GROUP BY a; the column list in the GROUP BY clause doesn’t convey much information. Of course you wanted to group by a, there is no other reasonable alternative. You can’t not group by a because that would be an error, and you can’t group by things besides a, because there is nothing else in the select list other than the aggregate. The problem gets worse if you have a longer select list or complicated expressions, because you need to repeat these in the GROUP BY clause and carefully keep them updated if you change something. (Or you can try to work around this by using subqueries.) Could be easier! A number of implementations have adopted the syntax GROUP BY ALL to simplify this. SELECT a, avg(b) FROM t1 GROUP BY ALL; The SQL standard working group discussed this feature informally at the June 2025 meeting, and there was consensus about going forward with it. At the September 2025 meeting, a formal change proposal was brought forward and accepted. (So, technically, it’s just a working draft right now, and it won’t be final until the standard is released.) The formal meaning of GROUP BY ALL is that it expands to a list of the elements of the select list that do not contain aggregate functions. So in SELECT a, avg(b) FROM t1 GROUP BY ALL; a does not contain an aggregate function, but avg(b) does, so GROUP BY ALL resolves to GROUP BY a, as expected. This doesn’t completely remove the need for explicit GROUP BY lists. Consider a more complicated case like CREATE TABLE t1 (a int, b int, c int, d int, ...); SELECT a, avg(b) + c + d FROM t1 GROUP BY ALL; Here, a does not contain an aggregate function, but avg(b) + c + d does contain an aggregate function, so the query resolves to SELECT a, avg(b) + c + d FROM t1 GROUP BY a; But that is not valid, because you need to account for the ungrouped co...