Re: Counting help

[Jennifer Wagner 301-827-3943 FAX 301-594-6463 <WAGNERJ@CDER.FDA.GOV>]

Thanks a ton!  I used a variation of the code that Arjan and George gave
me and possibly someone else (because I simplified all the different
things that were required) and then I tested it with a drug I knew only
had one active ingredient and the numbers matched!  Mucho thanks!

I'll put what I did here (like I said, I left out some of the
requirements before since they weren't totally necessary for the problem
and changed the field name to bdnum because it was shorter to write (and
that's what it is anyway)):

begin-procedure get_total_single
begin-select
count(p.appl_no) &cnt (+1,10)
from product p
where approval_type like 'APP%'
and p.appl_type = 'N'
and 1 = (select count(f.dprf_ingred_no) from formulation f
where f.appl_no = p.appl_no and f.appl_type = p.appl_type
and f.product_no = p.product_no and f.ingredient_type_code = 'AI')
end-select
end-procedure


Jenny



>Jennifer,
>
>this should work (I admit I copied a piece of George's
>code but translated it into sqr, I tested it with
>similar data on my system)
>
>begin-procedure main
>begin-select
>
>count(*)      &count1
>
>from  product p
>where p.approval_type = 'APP'
>and   1 = (select count(f.bdnum)
>           from   formulation f
>           where f.appl_no = p.appl_no
>          )
>end-select
>  show 'first count = ' &count1
>
>begin-select
>
>count(*)     &count2
>
>from product p
>where p.approval_type = 'APP'
>and   1 < (select count(f.bdnum)
>           from   formulation f
>           where f.appl_no = p.appl_no
>          )
>end-select
>  show 'second count = ' &count2
>
>  let #perc = &count1 / (&count1 + &count2) * 100.00
>
>  show 'percentage = ' #perc
>
>end-procedure
>~
>
>The shows are for testing of course... hope this helps
>
>Arjan
>
>
>
>
>
>--- Jennifer Wagner 301-827-3943 FAX 301-594-6463
><WAGNERJ@CDER.FDA.GOV> wrote:
>> Well, gurus, I apologize if this has been covered
>> before (can someone
>> resend the URL for the archives page?) or if this is
>> an asinine
>> questions, but here goes:
>>
>> I'm attempting to get a percentage.  Here's the
>> tricky part, I need a
>> count of drugs with a total of only one ingredient
>> and also a count of
>> drugs with more than one ingredient.
>>
>> Then I can get the percentage of RX drugs vs. OTC
>> drugs.
>>
>> I've tried all sorts of ways to go about this.  The
>> applicable fields
>> are appl_no (this would be the count of drugs) and
>> bdnum (which would
>> count the number of ingredients.
>>
>> See, each appl_no will be made up of a certain
>> amount of bdnums.  So, I
>> want to do something like count(appl_no) where
>> count(bdnum) = 1.
>>
>> The strange thing is, it seems to work if I do where
>> count(bdnum) >1.  I
>> come up with a total of like 20000 (which still
>> sounds low, tho).  But,
>> nothing returns back for = 1 and it should because
>> off the bat I know of
>> 8 drugs which are one active ingredient only.
>>
>> So, help, please?!
>>
>>
>> begin-procedure main
>> begin-select
>> count(p.appl_no)
>> from product p, formulation f
>> where p.appl_no = f.appl_no
>> and p.approval_type = 'APP'
>> and count(f.bdnum) = 1
>> end-select
>> end-procedure
>>
>
>__________________________________________________
>Do You Yahoo!?
>Talk to your friends online with Yahoo! Messenger.
>http://im.yahoo.com