CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Tregoreg
Tregoreg

Reputation: 22166

Empty GROUP BY in PostgreSQL

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?

Upvotes: 2

Views: 3203

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656716

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:

Upvotes: 4

Alexander Bolgov
Alexander Bolgov

Reputation: 81

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

Upvotes: 1

Related Questions