Re: query optimization question

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
WHENdt.days_old_start_date >= {d '2002-10-08'}                AND dt.days_old_start_date < {d '2002-09-08'}
THENlots.lot_id ELSE NULL END    ) AS def_count_30_60,COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
THENlots.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_idELSE 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
NULLEND)    ) AS lot_count_30_60,COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
THENlots.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
projectsWHERE 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_idAND
dt.lot_id= lots.lot_idAND dt.deficiency_status_id = ds.deficiency_status_idAND ds.is_outstandingAND lots.project_id =
'EM16'ANDNOT 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
>