Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8F477475F39 for ; Wed, 6 Nov 2002 12:11:35 -0500 (EST) Received: from ashtonwoodshomes.com (unknown [216.94.125.242]) by postgresql.org (Postfix) with ESMTP id DDABA475CA6 for ; Wed, 6 Nov 2002 12:11:34 -0500 (EST) Received: from greatgulfhomes.com. (IDENT:root@[216.191.203.109]) by ashtonwoodshomes.com (8.11.6/8.11.6) with ESMTP id gA6HVHo27518; Wed, 6 Nov 2002 12:31:17 -0500 Received: from terrynew2 (firewall.development.greatgulfhomes.com [216.191.203.106]) by greatgulfhomes.com. (8.11.6/8.11.6) with SMTP id gA6HE1R06076; Wed, 6 Nov 2002 12:14:01 -0500 From: To: "'Masaru Sugawara'" Cc: Subject: Re: query optimization question Date: Wed, 6 Nov 2002 12:10:33 -0500 Message-ID: <003f01c285b7$6b3c0480$2766f30a@development.greatgulfhomes.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 In-reply-to: <20021107014333.F0D0.RK73@sea.plala.or.jp> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/83 X-Sequence-Number: 10225 That looks really promising as a possibility, however I think you intended to add a group by clause. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: Masaru Sugawara [mailto:rk73@sea.plala.or.jp] > Sent: Wednesday, November 06, 2002 11:44 AM > To: terry@ashtonwoodshomes.com > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] query optimization question > > > On Wed, 6 Nov 2002 09:01:49 -0500 > wrote: > > > If anyone can see a way to do a group by to do this, then I > will be happy to > > hear about it, because currently the resultset has to do a separate > > (sequential or index) scan of the deficiencies table. The > only way I can > > see to do a group by would be to break out the aging categories into > > separate queries, but that wins me nothing because each > query then does its > > own scan... > > > > The expected simplified output of this query looks like this: > > Project <30 30-60 >=60 lot total <30 > 30-60 >=60 def total > > X 1 2 1 4 5 10 5 > 20 (if X had 4 lots, each of 5 deficiencies) > > Y 1 1 0 2 3 3 0 > 6 (each has eg 3 deficiencies in project Y) > > > > > The following query may be one of the ways, but I cannot > confirm whether > it goes well or not. > > > SELECT > project_id, > marketing_name, > COUNT(lots.lot_id) AS def_count, > COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'} > THEN lots.lot_id ELSE NULL END) AS > def_count_less_30, > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'} > AND dt.days_old_start_date < {d > '2002-09-07'} > THEN lots.lot_id ELSE NULL END) AS > def_count_30_60, > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'} > AND dt.days_old_start_date < {d > '2002-08-08'} > THEN lots.lot_id ELSE NULL END) AS > def_count_60_90, > COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'} > THEN lots.lot_id ELSE NULL END) AS > def_count_greater_90, > COUNT(DISTINCT(CASE WHEN > dt.days_old_start_date < > {d '2002-10-07'} > THEN lots.lot_id ELSE NULL END )) > AS lot_count_less_30, > COUNT(DISTINCT(CASE WHEN > dt.days_old_start_date >= > {d '2002-10-07'} > AND dt.days_old_start_date < {d > '2002-09-07'} > THEN lots.lot_id ELSE NULL END )) > AS lot_count_30_60, > COUNT(DISTINCT(CASE WHEN > dt.days_old_start_date >= > {d '2002-09-07'} > AND dt.days_old_start_date < {d > '2002-08-08'} > THEN lots.lot_id ELSE NULL END )) > AS lot_count_60_90, > COUNT(DISTINCT(CASE WHEN > dt.days_old_start_date >= > {d '2002-08-08'} > THEN lots.lot_id ELSE NULL END )) > AS lot_count_greater_90, > COUNT(DISTINCT lots.lot_id) AS lot_count > FROM > (SELECT * FROM deficiency_table > WHERE assigned_supplier_id = '101690') AS dt, > (SELECT * FROM deficiency_status, > WHERE ds.is_outstanding) AS ds, > (SELECT * FROM projects > WHERE proj.division_id = 'GGH') AS proj > lots > WHERE > dt.lot_id = lots.lot_id > AND lots.division_id = proj.division_id > AND lots.project_id = proj.project_id > AND dt.deficiency_status_id = ds.deficiency_status_id > AND NOT EXISTS > (SELECT 1 FROM menu_group_projects > WHERE menu_code = 'WA' > AND division_id = proj.division_id > AND project_id = proj.project_id > AND status = 'I') > ORDER BY proj.project_id > > > > > Regards, > Masaru Sugawara >