That looks really promising as a possibility, however I think you intended to add a group by clause.
Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -----Original Message----- > From: Masaru Sugawara [mailto:rk73@;sea.plala.or.jp] > Sent: Wednesday, November 06, 2002 11:44 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] query optimization question > > > On Wed, 6 Nov 2002 09:01:49 -0500 > <[EMAIL PROTECTED]> 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_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 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 > 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 > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
