From: <terry@ashtonwoodshomes.com>
To: "'Christoph Haller'" <ch@rodos.fzk.de>,
	<terry@ashtonwoodshomes.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: query optimization question
Date: Thu, 7 Nov 2002 18:18:02 -0500
Message-ID: <000001c286b3$ec0c6b20$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <3DCA2AF6.2D58D843@rodos.fzk.de>

Actually, the ORDER BY  *must* be replaced by GROUP BY since it is an
aggregate query.

I have implemented it, and the results are startling, I get the same value
repeated for all projects
i.e.:
AS1   AS1-AJAX/SALEM SIDE   3   0   6   7   30   0   216   240
AU3   AU3-RIVERIDGE/AURORA   3   0   6   7   30   0   216   240
AV1   AVALON   3   0   6   7   30   0   216   240
AW1   AW1-AJAX/WESTNEY SIDE   3   0   6   7   30   0   216   240
AWM   AW MORTGAGE   3   0   6   7   30   0   216   240
AX1   AX1-ROSE PETAL VALLEY DEV INC   3   0   6   7   30   0   216   240

And this appears to be the correct data row for a row that is the first
(alphabetically) project that has non zero data in it.


This is the final query, can anyone see anything wrong with it?:
SELECT  projects.project_id, projects.marketing_name,
	COUNT(lots.lot_id) AS def_count,
	COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
				THEN lots.lot_id ELSE NULL END
	) AS def_count_less_30,
		COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-08'}
					AND dt.days_old_start_date < {d '2002-09-08'}
				THEN lots.lot_id ELSE NULL END
		) AS def_count_30_60,
	COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
			THEN lots.lot_id ELSE NULL END
	) AS def_count_greater_60,
	COUNT(DISTINCT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
							AND dt.deficiency_status_id = ds.deficiency_status_id
						THEN lots.lot_id ELSE NULL END)
	) AS lot_count_less_30,
		COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d '2002-10-08'}
								AND dt.days_old_start_date < {d '2002-09-08'}
							THEN lots.lot_id ELSE NULL END)
		) AS lot_count_30_60,
	COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
						THEN lots.lot_id ELSE NULL END)
	) AS lot_count_greater_60,
	COUNT(DISTINCT lots.lot_id) AS lot_count
FROM
	(SELECT * FROM deficiency_table) AS dt,
	(SELECT * FROM deficiency_status WHERE is_outstanding) AS ds,
	(SELECT * FROM projects WHERE division_id = 'GGH') AS proj,
	(SELECT * FROM lots) AS lots
WHERE   proj.division_id = 'GGH'
    AND lots.division_id = proj.division_id
    AND lots.project_id = proj.project_id
	AND dt.lot_id = lots.lot_id
	AND dt.deficiency_status_id = ds.deficiency_status_id
	AND ds.is_outstanding
	AND lots.project_id = 'EM16'
	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')
GROUP BY projects.project_id, projects.marketing_name

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Christoph Haller
> Sent: Thursday, November 07, 2002 3:57 AM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] query optimization question
>
>
> >  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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>