Re: SQL query

Using PL/SQL to hold temporary result would be one way and simple, too. If you MUST do it in one SQL, here is one approach you can try....This is just an example
with dummy names - you replace table names etc.

    select (select count(*) from mytable where field1='PASS' and 'field2='key') /

       (select count(*) from mytable where field1 <>'PASS' and 'field2='key') from dual;
-Jon Yi

"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:23:56 CST