Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E2B66475E71 for ; Thu, 7 Nov 2002 03:57:36 -0500 (EST) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id DA7B5475D70 for ; Thu, 7 Nov 2002 03:57:35 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Nov 07 09:57:27 2002 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id JAA05950; Thu, 7 Nov 2002 09:57:27 +0100 (MET) Message-ID: <3DCA2AF6.2D58D843@rodos.fzk.de> Date: Thu, 07 Nov 2002 09:57:27 +0100 From: Christoph Haller X-Mailer: Mozilla 4.06 [en] (X11; I; HP-UX B.10.20 9000/780) MIME-Version: 1.0 To: terry@ashtonwoodshomes.com Cc: pgsql-sql@postgresql.org Subject: Re: query optimization question Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/87 X-Sequence-Number: 10229 > 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 is_outstanding) AS ds, > (SELECT * FROM projects > WHERE 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 ; What about simply replacing ORDER BY proj.project_id ; by GROUP BY project_id, marketing_name ; Regards, Christoph