Received: from nts1.rtp.epa.gov (nts1.rtp.epa.gov [134.67.208.208]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA93555 for ; Fri, 14 Jan 2000 16:14:24 -0500 (EST) (envelope-from volpe.mark@epamail.epa.gov) Received: from epamail.epa.gov (IDENT:mvs@wolfpack.rtpnc.epa.gov [134.67.136.18]) by nts1.rtp.epa.gov (8.9.3/8.9.3) with ESMTP id QAA15960 for ; Fri, 14 Jan 2000 16:17:15 -0500 (EST) Sender: mvs@nts1.rtp.epa.gov Message-ID: <387F923C.3E279281@epamail.epa.gov> Date: Fri, 14 Jan 2000 16:16:44 -0500 From: Mark Volpe X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.9-27mdk i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Aggregates and Views Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Is it possible to use an aggregate on a view that contains an aggregate? I can't seem to get this to work: (cut & paste into psql to reproduce) CREATE TABLE x ( n int ); INSERT INTO x VALUES(5); INSERT INTO x VALUES(5); INSERT INTO x VALUES(5); INSERT INTO x VALUES(10); INSERT INTO x VALUES(10); INSERT INTO x VALUES(10); INSERT INTO x VALUES(15); INSERT INTO x VALUES(15); INSERT INTO x VALUES(15); INSERT INTO x VALUES(15); INSERT INTO x VALUES(20); INSERT INTO x VALUES(20); SELECT count(*) from x; -- Correctly returns 12 CREATE VIEW y AS SELECT n, count(*) AS total FROM x GROUP BY n; SELECT * FROM y; -- Correctly returns (5,3), (10,3), (15,4), (20,2) -- So far so good -- but if I do this: SELECT count(*) FROM y; -- Instead of getting 4, I get 3,3,4,2 SELECT sum(total) FROM y; -- Returns nonsense values 0, 9, 12, 8 DROP TABLE x; DROP VIEW y; (end cut & paste) Thanks, Mark