On Sun, 18 Dec 2022 at 23:30, Tom Lane <t...@sss.pgh.pa.us> wrote: > Andrey Borodin <amborodi...@gmail.com> writes: > > I saw a thread in a social network[0] about GROUP BY ALL. The idea seems > useful. > > Isn't that just a nonstandard spelling of SELECT DISTINCT? >
In a pure relational system, yes; but since Postgres allows duplicate rows, both in actual table data and in intermediate and final result sets, no. Although I'm pretty sure no aggregates other than count() are useful - any other aggregate would always just combine count() copies of the duplicated value in some way. What would happen if there are aggregate functions in the tlist? > I'm not especially on board with "ALL" meaning "ALL (oh, but not > aggregates)". > The requested behaviour can be accomplished by an invocation something like: select (t).*, count(*) from (select (…field1, field2, …) as t from …tables…) s group by t; So we collect all the required fields as a tuple, group by the tuple, and then unpack it into separate columns in the outer query.
