Empty GROUP BY in PostgreSQL

Question

I have a table of association rules (say antecedent, succedent, and support). I would like to draw them in GraphViz. I have became so lazy that I don't want to code any imperative script for that, I would just like to select the DOT source directly in SQL.

My idea was as simple as

SELECT string_agg('"' || x || '" -> "' || y || '" [weight="' || weight || '"]', E'\n')
FROM rules
GROUP BY ...;

Then I realized I'm having surprising difficulties with the GROUP BY clause. Because I need to group all the rows, the clause should be left empty.

What is the most elegant way of saying GROUP BY NOTHING, i.e. perform the grouping on an empty set of columns?

@Lamak Aaah, very easy, thanks, thought I tried it but obviously I didn't. — Tregoreg
– Tregoreg, Commented Nov 7, 2016 at 17:11

Erwin Brandstetter · Accepted Answer · 2023-01-08 20:59:56Z

4

To aggregate all rows, you don't need to form groups with GROUP BY and can can just omit the GROUP BY clause. The manual:

If there are aggregate functions but no GROUP BY clause, the query is treated as having a single group comprising all the selected rows.

Or (if you are building the query string dynamically) you could use any constant expression like:

...
GROUP BY true

You can't use ~~GROUP BY 1~~ for this purpose because integer numbers serve as positional references (ordinal numbers) to SELECT list items in the GROUP BY clause:

Select first row in each GROUP BY group?

edited Jan 8, 2023 at 20:59

answered Nov 7, 2016 at 18:36

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jnas Over a year ago

A missing or constant GROUP BY raises an error when used with a ORDER BYclause.

Erwin Brandstetter Over a year ago

@jnas: An aggregate query without GROUP BY returns exactly one row. So why ORDER BY? Even if that occurs due to inflexible query concatenation: ORDER BY doesn't necessarily require and explicit GROUP BY. It works with output column names or ordinal positions. Only input column(s) would have to be listed in GROUP BY, but that would change the output for multiple distinct values in said input column(s), or else be just noise. And ORDER BY $constant never raises an error, it's a no-op.

Alexander Bolgov · Accepted Answer · 2021-06-29 16:22:48Z

1

You can use GROUPING SETS feature see documentation

Like so:

...
GROUP BY GROUPING SETS ((<your comma-separated list of columns>))

so if your list of columns is empty, this ends up looking like GROUP BY GROUPING SETS (()) what happens to be a valid syntax, behaving the same as having no GROUP BY at all

edited Jun 29, 2021 at 16:22

answered Jun 29, 2021 at 13:52

Alexander Bolgov

815 bronze badges

Collectives™ on Stack Overflow

Empty GROUP BY in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related