Re: query optimization question

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. 


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_idELSE 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
NULLEND )) 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_idELSE 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 ds.is_outstanding) AS ds,  (SELECT * FROM projects              WHERE
proj.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
WHEREmenu_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