Aggregates and Views
От
Mark Volpe
Тема
Aggregates and Views
Дата
Msg-id
387F923C.3E279281@epamail.epa.gov
Список
Дерево обсуждения
Aggregates and Views Mark Volpe <volpe.mark@epamail.epa.gov>
Re: [SQL] Aggregates and Views Tom Lane <tgl@sss.pgh.pa.us>
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
В списке pgsql-sql по дате отправления