Now we'll move on to the medications. Let's add a feature that simply tallies the number of medications each patient is taking. In Query Set #5 (as follows) we first add the Num_meds column using an ALTER TABLE statement. Then, we use a SELECT-FROM-WHERE block inside of an UPDATE statement to find the number of medications for each patient. The query works by tallying, for each patient ID in the MORT_FINAL_2 table, the number of rows in the MEDICATIONS table where the corresponding patient ID is equal. Again, we use the COUNT function to get the number of rows. We introduce a new function in this query, DISTINCT. DISTINCT removes any rows containing duplicate values for the column in parentheses. So for example, if LISINOPRIL was listed twice for a patient, the DISTINCT(Rx_name) function call would ensure it is only counted once:

sqlite> ALTER TABLE MORT_FINAL_2 ADD COLUMN Num_meds INTEGER;

sqlite> UPDATE MORT_FINAL_2 SET Num_meds = 
(SELECT COUNT(DISTINCT(Rx_name)) 
FROM MEDICATIONS AS M
WHERE MORT_FINAL_2.Pid = M.Pid);