2

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?

Tregoreg
  • 18,872
  • 15
  • 48
  • 69

2 Answers2

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    A missing or constant `GROUP BY` raises an error when used with a `ORDER BY`clause. – jnas Jan 09 '20 at 16:04
  • @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. – Erwin Brandstetter Jan 09 '20 at 16:17
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

Alexander Bolgov
  • 81
  • 5