Date: Thu, 07 Nov 2002 02:11:42 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Masaru Sugawara <rk73@sea.plala.or.jp>
Subject: Re: query optimization question
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20021107014333.F0D0.RK73@sea.plala.or.jp>
References: <002601c2859d$0d825480$2766f30a@development.greatgulfhomes.com>
	<20021107014333.F0D0.RK73@sea.plala.or.jp>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20021107020210.F0D7.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

On Thu, 07 Nov 2002 01:44:25 +0900
I wrote  <rk73@sea.plala.or.jp> wrote:

> On Wed, 6 Nov 2002 09:01:49 -0500
> <terry@ashtonwoodshomes.com> 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. 
> 
> 

There are some misspelling in FROM clause. Now  they are fixed.


 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
 

Regards,
Masaru Sugawara