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:
SELECT DISTINCT A.PERSON FROM
(SELECT DISTINCT PERSON FROM PBASE) A
(SELECT DISTINCT PERSON FROM DRUG1) B
ON A.PERSON = B.PERSON
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" "
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')
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.