From: Christoph Haller <ch@rodos.fzk.de>
To: terry@ashtonwoodshomes.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query optimization question
Date: Thu, 07 Nov 2002 09:57:27 +0100
Message-ID: <3DCA2AF6.2D58D843@rodos.fzk.de> (raw)
> 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
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: ch@rodos.fzk.de, terry@ashtonwoodshomes.com
Subject: Re: query optimization question
In-Reply-To: <3DCA2AF6.2D58D843@rodos.fzk.de>
* 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