Re: query optimization question - Mailing list pgsql-sql

>  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 ; byGROUP BY project_id, marketing_name ;

Regards, Christoph