Re: query optimization question

>
> 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 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 NULL END
> ) AS def_count_30_60,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-08'}
> THEN lots.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_id ELSE
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 NULL END)
> ) AS lot_count_30_60,
> COUNT(DISTINCT(CASE WHEN dt.days_old_start_date >= {d
'2002-09-08'}
> THEN lots.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 projects WHERE 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_id
> AND dt.lot_id = lots.lot_id
> AND dt.deficiency_status_id = ds.deficiency_status_id
> AND ds.is_outstanding
> AND lots.project_id = 'EM16'
> 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')
> GROUP BY projects.project_id, projects.marketing_name
>
First thing I would try
change
> SELECT projects.project_id, projects.marketing_name,
to
SELECT proj.project_id, proj.marketing_name,
and
> GROUP BY projects.project_id, projects.marketing_name
to
GROUP BY proj.project_id, proj.marketing_name
because I think the sub-SELECT (SELECT * FROM projects WHERE division_id
= 'GGH') AS proj
should be referenced instead of the table projects.
If you still receive the startling result, I'd like to suggest another
approach.
Why not generate a view or sub-SELECT first which shows all the columns
you need to refer to resp. count, and then
SELECT project_id, marketing_name,
COUNT( ... ,
... ,
FROM < the view or sub-SELECT>
-- no WHERE-clauses at all
GROUP BY project_id, marketing_name ;

It should at least make it more easy to track down what's wrong.

Regards, Christoph