SQL query

From: Butt, Naseem [CAR:7622:EXCH] <nbutt_at_americasm01.nt.com>
Date: Thu, 21 Feb 2002 09:33:52 -0500
Message-ID: <a530fb$2hk$1@bcarh8ab.ca.nortel.com>

I'm feeling a bit 'brain dead' and need some assist with an sql query. I have a table called 'bhet', with many columns. Two of the columns are called 'Waferid' and 'failcode'. Now 'failcode' can either be a 'PASS' or another string variable. I want to extract from this table by waferid, all the passes, and all the fails and then do a simple yield calc where yield =passes/passes+fails.

Any suggestions would be appreciated.

PS This is my effort so far which does not give me the desired result:

select a.waferid, a.failcode, count(a.failcode)as count_fails, count(b.failcode) as count_passes
from oc48_bhet_param_master a,oc48_bhet_param_master b where a.failcode <> 'PASS'
and b.failcode = 'PASS'
and a.retestindex = (select max(a.retestindex)

                   from oc48_bhet_param_master b,oc48_bhet_param_master c
                   where b.deviceid = c.deviceid)

group by rollup(a.waferid,a.failcode);

Nas. Received on Thu Feb 21 2002 - 08:33:52 CST