Camm Maguire <camm@enhanced.com> writes:
> Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
>> CREATE VIEW my view AS
>> SELECT id, partialsum, (partialsum/totalsum) AS percentage
>> FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
>> JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause
> I can't seem to get this syntax to work with pg. No subselects seem
> to be accepted in the from clause, and join doesn't seem to be a
> reserved word at all.
Sounds like you are trying to do it in 7.0 or before. Emmanuel is
relying on 7.1 features --- and the example won't work as given anyway,
since (a) the subselects neglect to specify source tables; (b) you
have to write CROSS JOIN not JOIN if you want to omit ON/USING.
In 7.0 you could accomplish the same thing with temp tables, or more
straightforwardly by something like
SELECT id,
SUM(item) AS partialsum,
SUM(item) / (SELECT SUM(item) FROM table) AS percentage
FROM table
GROUP BY id
This relies for efficiency on the poorly-documented fact that the
sub-select will only be evaluated once, since it has no dependency
on the state of the outer select. (You can check this by seeing that
EXPLAIN shows the subselect as an InitPlan not a SubPlan.)
regards, tom lane