Re: problem with subqueries

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?