Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA13806 for ; Fri, 14 Jan 2000 17:26:20 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id RAA01307; Fri, 14 Jan 2000 17:26:17 -0500 (EST) To: Mark Volpe cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Aggregates and Views In-reply-to: <387F923C.3E279281@epamail.epa.gov> References: <387F923C.3E279281@epamail.epa.gov> Comments: In-reply-to Mark Volpe message dated "Fri, 14 Jan 2000 16:16:44 -0500" Date: Fri, 14 Jan 2000 17:26:16 -0500 Message-ID: <1304.947888776@sss.pgh.pa.us> From: Tom Lane Mark Volpe writes: > Is it possible to use an aggregate on a view > that contains an aggregate? Not at present. Views are implemented by a rewriter that tries to transform your query into another SQL query, and since aggregates of aggregates are not possible, it doesn't work. In current sources I actually get an error from your second example: regression=# SELECT sum(total) FROM y; ERROR: Aggregate function calls may not be nested For the same sort of reason, GROUPed views don't play nice with an outer query that specifies different grouping (ie, has a GROUP clause of its own, or perhaps an aggregate). What we need in order to fix this is subselects in FROM clauses; if the rewriter could transform your query into SELECT sum(total) FROM (SELECT n, count(*) AS total FROM x GROUP BY n) then everything would just work (and the rewriter would get a lot simpler, too ;-)). We don't have subselects in FROM yet, but I hope to see them in 7.1, or 7.2 at the latest. regards, tom lane