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

brucezepplin · #1 (**permalink**) 09-16-11, 10:20

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!

brucezepplin · #2 (**permalink**) 09-16-11, 10:29

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.

ARWinner · #3 (**permalink**) 09-16-11, 11:35

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

tonkuma · #4 (**permalink**) 09-16-11, 11:43

Please supply sample data for your 6 tables and the final results which you want to get from the sample data.

brucezepplin · #5 (**permalink**) 09-16-11, 12:10

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech