Re: problem with subqueries

pete(at)phillipsfamily(dot)freeserve(dot)co(dot)uk writes:
> select distinct year,month,
> (select sum(monthcustomer.number_of_items) from monthcustomer where
> monthcustomer.year=m.year and monthcustomer.month=m.month) as NumPots
> from monthcustomer m;

> This goes off and never comes back -

No surprise, considering the sub-select is going to be evaluated
separately for every row of monthcustomer --- and then most of those
evaluations will be thrown away by the DISTINCT :-(

A straightforward way of reducing the redundant computations would be
to do the DISTINCT first:

select year,month,
(select sum(monthcustomer.number_of_items) from monthcustomer where
monthcustomer.year=m.year and monthcustomer.month=m.month) as NumPots
from
(select distinct year, month from monthcustomer) as m;

But it appears to me that you are reinventing the wheel. Isn't this
query the equivalent of a grouped aggregation --- viz,

select year, month, sum(number_of_items) as NumPots
from monthcustomer
group by year, month

regards, tom lane