Re: onlyvalue aggregate (was: First Aggregate Funtion?)

On Wed, Oct 28, 2015 at 5:03 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> SELECT a, sum(amount), onlyvalue(rolling_count)
> FROM
> (
> SELECT a, amount, count(*) OVER (ORDER BY a) AS rolling_count
> FROM tbl
> ) ss
> GROUP BY a;

The same thing would happen even in the more common case of having
functionally dependent columns if they happen to be buried in a
subquery. That might well be convenient if you have some expression
you want to use in multiple aggregates such as:

SELECT pk, acol, avg(x), min(x), max(x)
FROM (
SELECT a,pk, a,acol, b.c+b.d+b.e AS x
FROM a JOIN b ON (a.pk = b.fk)
)
GROUP BY pk

Postgres would happily accept that if you collapsed the subquery and
ran the group by directly on the join but the subquery in between is
actually enough to hide the functional dependency information so it
complains that acol is not functionally dependent on the group by
column.

--
greg