Re: query optimization question - Mailing list pgsql-sql

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
NULLEND) 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_countFROM   (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,   lotsWHERE   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