Tregoreg Tregoreg - 1 year ago 79
SQL Question

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

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

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

Answer Source

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 can use any constant expression like:


You can't use GROUP BY 1 for this purpose because integer constants serve as positional references (ordinal numbers) in GROUP BY: