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]),
> ()
