Thread: aggregates of aggregates

Does anyone know of any plans to support aggregates of aggregates in the
future? It would be great to be able to select with an aggregate function
from another select statement with another aggregate function. I have
tried putting the result of an aggregate function in a view and then
performing another aggregate function on the view, but I get the error
that nested aggregates are not supported.
My solution was to put the result of the first aggregate function in a
temp table, then doing the other select statement on the temp table. This
is not very efficient as the temp table can have up to a million records
in it.
Is this feature planned for Postgres? Does anyone know a better workaround
than the way I am doing it?

Thanks in advance,

-Tor

Tor Roberts <pg@econsci.net> writes:
> Does anyone know of any plans to support aggregates of aggregates in the
> future?

Not directly: it makes no sense in the SQL semantic model.  We do have
plans to support subselect-in-FROM (hopefully in 7.2), which would allow
constructs like

SELECT count(*) FROM (SELECT foo,count(*) FROM bar GROUP BY foo)

The same revisions will also be needed before views that involve
grouping or aggregates will work right in all but the simplest cases.

> My solution was to put the result of the first aggregate function in a
> temp table, then doing the other select statement on the temp table. This
> is not very efficient as the temp table can have up to a million records
> in it.

Sounds like a good workaround for now.

            regards, tom lane