Help writing query

[michaelgr1]

Hello, I need to write a query. I have a table with many operators (can take them using a query) and for each of them i need to calculate it's success rate (count fail units query/count all units query). I can do the calculation of the success rate for every operator, but how can i write a query that will loop through all the operators and show the success rate of each operator (in one table). The operators and the units (count for fail and all the units) are located in one table in the DB. The results will be as follows: there will be an operator columns and success rate column. In the operator column it will write the operators from the table and in the second column it will write the success rate of each operator. How can i do it? I use the following query for the success rate (of one operator): select (convert(float,(select count(distinct jobid) from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and [operator] like 'mgroiser' and equipname like '%fib%' and equipname not like '%test%' and iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult='success')))/(select count(distinct jobid) from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and [operator] like 'mgroiser1' and equipname like '%fib%' and equipname not like '%test%' and iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult is not null) *100 I tried to write as follows: select (convert(float,sum(case when jobresult='success' then 1 else 0 end))/ count(distinct jobid)) * 100 from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and equipname like '%fib%' and equipname not like '%test%' and iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult is not null group by [operator] But it doesn't work well because i muse use a distinct in the sum(case...) too.

[Mycroft Holmes]

Thats one, no two ugly queries. You have a couple of options, you can move this into a cursor and insert the operator information into a table variable. I hate cursors and would not recommend this. I would move the operator information out of the where clause into the select areas and join the 2 result sets by operator. This will allow you to use group by operator to get the results without using a cursor. Research GROUP BY and INNER JOIN You are probably going to have to muck with the calc as well.

Never underestimate the power of human stupidity RAH