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