MySQL - Join 2 tables and count number of entries-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

Based on Stu-'s answer, I want to say that your column naming is wrong.But you can write query based on logic like this.

SELECT trial.title AS Trial_Name, COUNT(p.id) AS Num_Patients
FROM trial
INNER JOIN patients AS p
ON trial.patient_fk_id = p.id
GROUP BY trial.title,p.id;