MySQL – Join 2 tables and count number of entries

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.

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;