Re: SQL query

select waferid,
 sum(case when failcode='PASS' then 100 else 0 end)/count(*)

    ,'% in a sample of '
    ,count(*) from
 bhet group by waferid;

should give your percentage passes and sample sizes in each waferid. But your attempt below at a glance looks as if its doing something much more complicated than your text description so maybe I'm misunderstanding your requirement. There are probably some much better built-in functions for this kind of stuff than the platform-free SQL above.

"Butt, Naseem [CAR:7622:EXCH]" <nbutt_at_americasm01.nt.com> wrote in message news:a530fb$2hk$1_at_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 - 09:17:02 CST