Re: Counting help

[Arjan Hoornstra <ahoor@YAHOO.COM>]

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