Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 13960475921 for ; Sun, 6 Oct 2002 06:15:32 -0400 (EDT) Received: from cmailg2.svr.pol.co.uk (cmailg2.svr.pol.co.uk [195.92.195.172]) by postgresql.org (Postfix) with ESMTP id DD3C8475C15 for ; Sun, 6 Oct 2002 06:15:27 -0400 (EDT) Received: from [195.92.67.23] (helo=mail18.svr.pol.co.uk) by cmailg2.svr.pol.co.uk with esmtp (Exim 3.35 #1) id 17y8Rd-0003LH-00 for pgsql-sql@postgresql.org; Sun, 06 Oct 2002 11:15:29 +0100 Received: from modem-8.brown-bird-wrasse.dialup.pol.co.uk ([62.136.242.8] helo=phillipsfamily.freeserve.co.uk) by mail18.svr.pol.co.uk with esmtp (Exim 3.35 #1) id 17y8Ra-0005OZ-00 for pgsql-sql@postgresql.org; Sun, 06 Oct 2002 11:15:27 +0100 Received: from localhost (pete@localhost) by phillipsfamily.freeserve.co.uk (8.11.6/8.11.6) with ESMTP id g96BDfG10468; Sun, 6 Oct 2002 11:13:41 GMT Message-Id: <200210061113.g96BDfG10468@phillipsfamily.freeserve.co.uk> To: pgsql-sql@postgresql.org Cc: tgl@sss.pgh.pa.us, kenzo@kennethambrose.com Subject: Re: problem with subqueries References: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk> <6763.1033866093@sss.pgh.pa.us> Date: Sun, 06 Oct 2002 11:13:41 +0000 From: pete@phillipsfamily.freeserve.co.uk X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/76 X-Sequence-Number: 9794 Hi guys. Thanks for the rapid replies so far. To answer some of the questions: >you did not indicate an explicit join - or even a "from" clause for that >matter- in the example of your create view statement. My original post was a simplified version. Here is the actual view creating statement: create view monthord as select ord_date, extract (month from ord_date) as month, extract (year from ord_date) as year,r_region, number_of_items from orders,customer where ccode = codenum; >But it appears to me that you are reinventing the wheel. Isn't this >query the equivalent of a grouped aggregation Yes - but again I was simplifying - I want to run a sub query for each region, so I get output like this: year month Reg1 Reg2 Reg3 Reg4 ----- ----- ---- ---- ----- ---- 1999 Jan 20 45 10 27 1999 Feb 30 43 18 37 ... 2002 Oct 7 89 60 17 The subquery I have tried to run is actually this (there is probably a way to do this all in SQL, but at present I would like to just understand why my subqueries take so long). -----start of script --------------- #!/bin/sh # prepare a query as shell variables # set up timeframe SDATE=`date --date '2 years ago' +'%Y-%m-01'` EDATE=`date --date 'next month' +'%Y-%m-01'` # use the QUERY env variable to build the full query QUERY="select distinct year,month, " for reg in `psql -U postgres -d product_db -c "select distinct r_code from regionlist order by r_code;"` do QUERY="$QUERY (select sum(monthcustomer.number_of_items) from monthcustomer where monthcustomer.year=m.year and monthcustomer.month=m.month and r_region like '$reg') as $reg," done # now add a total column QUERY="$QUERY (select sum(monthcustomer.number_of_items) from monthcustomer where ord_date >= '$SDATE' and ord_date < '$EDATE' and monthcustomer.year=m.year and monthcustomer.month=m.month) as TOTAL from monthcustomer m;" # execute the query psql -U postgres -d newmaggot -c "$QUERY" -----end of script --------------- As you can see, I get all the regions, and loop through them building up a bunch of subqueries as I go along. Hopefully this will answer some of your questions. I will try some of the suggestions later on today (such as placing the distinct clause at the end) but first I have to go and get some decorating done :-( (yes, that sound you can hear is the crack of my wife's whip!). Regards, Pete -- I do whatever the Voices tell me to do