From: terry@ashtonwoodshomes.com
To: 'Christoph Haller' <ch@rodos.fzk.de>
To: 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> (raw)
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com, ch@rodos.fzk.de
Subject: Re: query optimization question
In-Reply-To: <000001c286b3$ec0c6b20$2766f30a@development.greatgulfhomes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox