On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 2012-09-18 5:53 AM, Larry Martell wrote: >> >> I have this query: >> >> SELECT data_target.name as Target, >> q1.ep as EP, >> COUNT(*) as Wafers, >> Lots, >> SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns >> ELSE 0 END)) as 'Sites/Wafer', >> MAX(LastRun) as "Last Run", >> SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Rerun, >> COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Runs, >> avgbottom as "Avg Bottom", >> 3*stdbottom as "3 Sig", >> maxbottom as Max, >> minbottom as Min, >> SUM(numonep) as Count, >> SUM(numonep) - SUM(numbottoms) as NAs, >> 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as "% >> Success", >> 3*stdbottom/avgbottom as "3Sig/Avg", >> AVG(avgbottom) as 'Wafer Avg', >> AVG(Wafer3Sigma) as 'Wafer 3 Sigma', >> AVG(Ranges) as 'Avg Range', >> 3*STD(Ranges) as '3Sig of Ranges', >> MAX(Ranges) as 'Max Range', >> MIN(Ranges) as 'Min Range', >> (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' >> FROM (SELECT target_name_id, >> ep, >> wafer_id, >> COUNT(bottom) as numbottoms, >> AVG(bottom) as avgbottom, >> STD(bottom) as stdbottom, >> MAX(bottom) as maxbottom, >> MIN(bottom) as minbottom, >> MAX(date_time) as "LastRun", >> COUNT(*) as numonep, >> COUNT(DISTINCT target_name_id, ep, lot_id, >> data_file_id)-1 as reruns, >> COUNT(DISTINCT(lot_id)) as Lots, >> 3*STD(bottom) as Wafer3Sigma, >> MAX(bottom) - MIN(bottom) as Ranges >> FROM data_cst >> WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, >> 116, 117, 118, 119, 120, 121) >> AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND >> '2010-03-04 23:59:59' >> GROUP BY target_name_id, ep, wafer_id >> HAVING count(*) < 999) q1, >> data_target >> WHERE data_target.id = target_name_id >> GROUP BY q1.target_name_id, q1.ep; >> >> >> This works fine. But now I need to get a certain column >> (image_measurer_id) with each row returned that corresponds to the row >> from the group that has bottom = Min(bottom), bottom = Max(bottom), >> bottom closest to Avg(bottom), and bottom from the row where date_time >> = Max(date_time). >> >> Is this even possible from one query? > > > Might be, but what's the importance of doing it as one query?
Because it's part of a django based web app, and the class that this is part of only supports having a single query. To use multiple queries will require a fairly major rewrite of the server side of that app. > I'd start by > saving this result to a temp table and developing the new query. When that's > running, see if you can to optimise a query built by replacing the reference > to the temp table with the original query. Thanks, I'll look into this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
