Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: adding " +1 " to a field if YES (please read due to rubbish description!)

    Hi guys, I'll attempt to explain the thread a title a bit better. I am trying to identify the effects of using certain prescribed drugs when taken together. Sp I have 5 different drugs, let's say drug 1,2...etc and I need a way to group together all people who have only taken 1 (random) drug, all people who have taken 2 (random) drugs etc etc up to 5 (random) drugs. So explicit combinations of certain drugs do not matter just yet. I have a database for all patients, and I have separate tables for all people using a certain drug that are in the general database, so I have something like:

    Code:
    SELECT DISTINCT A.PERSON FROM
    
    (SELECT DISTINCT PERSON FROM PBASE) A
    
    LEFT JOIN
    
    (SELECT DISTINCT PERSON FROM DRUG1) B
    ON A.PERSON = B.PERSON
    
    LEFT JOIN 
    
    SELECT DISTINCT PERSON FROM DRUG2) C
    ON A.PERSON = C.PERSON
    .
    .
    .
    .
    .
    .
    --and so on
    So I'll end up with a table of 6 columns where the first is a list of the people in the general database, and the other 5 are columns where a "YES" is in the field if that person is on that particular drug.

    Now the tricky part.....(well for me anyway)

    I would like to add a 7th columns that counts all of the "YES" fields and gives me a total of the amount of drugs they have taken so that I get the entire combination of drugs they have been using.

    Anyone have any suggestions how to do this? Hence the title of the thread being " adding +1 if "YES" "

    Any help will be much appreciated!

  2. #2
    Join Date
    Jul 2011
    Posts
    33
    I suppose I should be throwing the word increment around if am thinking of checking the field for a "YES" value and then adding 1 onto column 7 before moving onto the next column.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think this is what you want:

    Code:
    alter table pbase add column num_drugs int;
    
    update pbase as p set num_drugs = 
    (select count(*) from drug1 as d1 where d1.person =p.person and d1.is_taking = 'YES')
    +
    (select count(*) from drug2 as d2 where d2.person =p.person and d2.is_taking = 'YES')
    +
    (select count(*) from drug3 as d3 where d3.person =p.person and d3.is_taking = 'YES')
    .
    .
    .
    Andy

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please supply sample data for your 6 tables and the final results which you want to get from the sample data.

  5. #5
    Join Date
    Jul 2011
    Posts
    33
    The data across all tables simply contain an ID number (Person). The pbase table contains every ID number as it holds everyone. The other 5 columns contain ID numbers for people who have taken that specific drug. I simply want to count for each row, how many times the ID number in column one appears in any of the other 5 columns, and create a 7th column to show the total. So if someone from column 1 appears in columns 3 and 5, I want column 7 to say "2".

    AR - will try that now. I had in mind a variable such as @counter. But will try your way. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •