To: pete@phillipsfamily.freeserve.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: problem with subqueries 
In-reply-to: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk> 
References: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk>
Comments: In-reply-to pete@phillipsfamily.freeserve.co.uk
	message dated "Sat, 05 Oct 2002 22:19:31 -0000"
Date: Sat, 05 Oct 2002 21:01:33 -0400
Message-ID: <6763.1033866093@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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 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