Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E9D4B475FDE for ; Sat, 5 Oct 2002 17:19:18 -0400 (EDT) Received: from cmailg1.svr.pol.co.uk (cmailg1.svr.pol.co.uk [195.92.195.171]) by postgresql.org (Postfix) with ESMTP id D4383475AAC for ; Sat, 5 Oct 2002 17:19:16 -0400 (EDT) Received: from [195.92.67.23] (helo=mail18.svr.pol.co.uk) by cmailg1.svr.pol.co.uk with esmtp (Exim 3.35 #1) id 17xwKU-00041y-00 for pgsql-sql@postgresql.org; Sat, 05 Oct 2002 22:19:18 +0100 Received: from modem-153.clown-sweetlips.dialup.pol.co.uk ([62.136.248.153] helo=phillipsfamily.freeserve.co.uk) by mail18.svr.pol.co.uk with esmtp (Exim 3.35 #1) id 17xwKR-0007Li-00 for pgsql-sql@postgresql.org; Sat, 05 Oct 2002 22:19:16 +0100 Received: from localhost (pete@localhost) by phillipsfamily.freeserve.co.uk (8.11.6/8.11.6) with ESMTP id g95MJVx01588 for ; Sat, 5 Oct 2002 22:19:31 GMT Message-Id: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk> To: pgsql-sql@postgresql.org Reply-To: pete@phillipsfamily.freeserve.co.uk Subject: problem with subqueries Date: Sat, 05 Oct 2002 22:19:31 +0000 From: pete@phillipsfamily.freeserve.co.uk X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/73 X-Sequence-Number: 9791 Hi Any help appreciated - I have spent 2 days trying to get this query to work! I have an orders database and a customer database. The orders database has a date field for each order. 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 this run for 10-20 minutes and it is still running). I have indexes on the columns involved from the original tables. Any help appreciated. Pete PS: Using pgsql 7.2-70 from Suse distribution.