I’m trying to join 2 tables and count the number of entries for unique variables in one of the columns. In this case I’m trying to join 2 tables – patients
and trials
(patients has a FK to trials) and count the number of patients that show up in each trial. This is the code i have so far:
SELECT patients.trial_id, trials.title FROM trials JOIN(SELECT patients, COUNT(id) AS Num_Enrolled FROM patients GROUP BY trials) AS Trial_Name;
The Outcome I’m trying to acheive is:
Trial_Name Num_Patients Bushtucker 5 Tribulations 7
I’m completely new to sql and have been struggling with the syntax compared to scripting languages.
Advertisement
Answer
It’s not 100% clear from your question of the names of your columns however you are after a basic aggregation. Adjust the names of the columns if necessary:
select t.title Trial_Name, Count(*) Num_Patients from Trials t join Patients p on p.Trial_Id = t.Id group by t.title;