Question 112776 in Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search