Re: Counting help

[Mark Risman <mrisman@PAINEWEBBER.COM>]

How about this:

I assume your product table contains one entry per product, and each of those products is identified by a field called KEY (for argument's sake) in the product table.  George's solution would work, but if the subqueries slow you down, here's another possibliity

For one active ingredient:
! optional:
begin-declare
integer #one_ingredient #multiple_ingredients
end-declare
.
.
.
let #one_ingredient = 0
begin-select
p.KEY
count(*)
        add 1 to #one_ingredient
from product p, formulation f
where p.appl_no = f.appl_no
and p.approval_type = 'APP'
group by p.KEY
having count(*) > 1
end-select

let #multiple_ingredients = 0
begin-select
p.KEY
count(*)
        add 1 to #multiple_ingredients
from product p, formulation f
where p.appl_no = f.appl_no
and p.approval_type = 'APP'
group by p.KEY
having count(*) > 1
end-select

As Enrique mentioned, the HAVING acts like a WHERE clause, but applies to aggregated values; e.g. if you tried where count(*) > 1 here instead of having count(*) > 1, I'd expect a syntax error.

Hope this helps,
- Mark

                -----Original Message-----
                From:   Jennifer Wagner 301-827-3943 FAX 301-594-6463 [mailto:WAGNERJ@cder.fda.gov]
                Sent:   Friday, March 17, 2000 11:00 AM
                To:     Mark Risman
                Subject:        Re: Counting help
                Sensitivity:    Confidential

                No, there is tons of information in the formulation table.  Like route
                and dosage and potency.  I only want the bdnum field because that is the
                number of the exact ingredient.  I can't count the whole table.

                I'm trying to find the count of drugs which contain one ingredient,
                which would mean counting the appl_no (each drug has an appl_no) and
                bdnum(which is the number for the ingredient(s).

                For example, drug number 12345 could contain 4 ingredients: ingred1
                (bdnum 123), ingred2 (bdnum 345)...etc.

                Is that clearer?

                Thanks for the help.


                >Where are the ingredients listed?  Is there one record per ingredient
                in the formulation table?  Also, usually count (<field>) usually won't
                give you any new information that you won't get from count(*).  What are
                you trying to do there?
                >
                >- Mark
                >
                >               -----Original Message-----
                >               From:   Jennifer Wagner 301-827-3943 FAX 301-594-6463
                [mailto:WAGNERJ@CDER.FDA.GOV]
                >               Sent:   Friday, March 17, 2000 10:17 AM
                >               To:     SQR-USERS@list.iex.net
                >               Subject:        Counting help
                >               Sensitivity:    Confidential
                >
                >               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