Re: problem with subqueries

pete@phillipsfamily.freeserve.co.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 NumPotsfrom    (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 NumPotsfrom monthcustomergroup by year, month
        regards, tom lane