Date: Sat, 5 Oct 2002 18:02:09 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
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>
Message-ID: <20021005175906.R58075-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Sat, 5 Oct 2002 pete@phillipsfamily.freeserve.co.uk wrote:

> Because I want to obtain a monthly breakdown, I created a view called
> monthcustomer as this select:
>
> 	select orders.ord_date, customer.cname,
> 	date_part('month',orders.ord_date) AS "month",
> 	date_part('year',orders.ord_date) AS "year",
> 	orders.number_of_items;
>
> Each month will have multiple numbers of items, so to get a monthly
> breakdown I tried this:
>
> 	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 - CPU is hitting the top of the
> chart! I have to ^C to interrupt it, as it runs for ages (I've left

That's going to run that inner select once for every row in monthcustomer
probably.

Would
select year, month, sum(monthcustomer.number_of_items) as NumPots frmo
monthcustomer m group by year, month;
have the same effect, get the sum of the items for each year/month
combination along with which year and month?