On Sat, 6 Jan 2024 at 23:27, Geoff Winkless <pgsqlad...@geoff.dj> wrote:
> Well yes. I assumed that since it's required that a group expression is in > the query itself that > the grouping values were taken from the result set, I have to admit to some > surprise that > they're calculated twice (three times?). Seems there was a reason why I thought that: per the documentation: "The arguments to the GROUPING function are not actually evaluated, but they must exactly match expressions given in the GROUP BY clause of the associated query level." https://www.postgresql.org/docs/16/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE Mildly interesting: you can pass column positions to GROUP BY and ORDER BY but if you try to pass a position to GROUPING() (I wondered if that would help the engine somehow) it fails: SELECT test1.n, CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) FROM test1 GROUP BY GROUPING SETS( 1, 2 ) ORDER BY CASE WHEN GROUPING(1)=0 THEN 1 ELSE NULL END NULLS FIRST, CASE WHEN GROUPING(2)=0 THEN 2 ELSE NULL END NULLS FIRST; ERROR: arguments to GROUPING must be grouping expressions of the associated query level Geoff
