Of course, right when I hit send I thought of another option that makes the
SQL a little less readable but perhaps gets rid of the ambiguity. Using
ordinals in the GROUP BY:
SELECT path[1], path[2], path[3], path[4], sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(1, 2, 3, 4),
(1,
Aner Perez writes:
> Or if using indexed path elements in the GROUP BY is the issue and I should
> put the array indexing in a subselect and do the aggregation in the outer
> select. Like this:
> -- Safer Subselect Version --
> SELECT level1, level2, level3, level4, sum(value)
> FROM (
> SEL
Thanks Tom,
I figured as much about the second query but I thought it would be safe to
use the first version with the inlined indexing.
I'm not sure if you're saying that the same query without the unindexed
path column in the select would be safe. Like this:
-- Do not GROUP BY or SELECT on path
Aner Perez writes:
> [ these queries don't give the same results: ]
> SELECT path[1], path[2], path[3], path, sum(value)
> FROM bind_group_by
> GROUP BY GROUPING SETS (
> (path[1], path[2], path[3], path),
> (path[1], path[2], path[3]),
> (path[1], path[2]),
> (path[1]),
> ()
I wrote a query for a report that needed to summarize table data for
different subsets of an ARRAY column. Once I had a working query, I
recreated it in my code using an SQL query builder (the awesome jOOQ in
this case). Unfortunately the output from the generated SQL was different
from the handw
