Inner join 3 tables and calculate the average value on a field

Question

There are 3 tables:

1. medics:
   • IDM (id_medic) (primary key)
   • 1st name
   • 2nd name
   • specialty
2. patients:
   • IDP (id_patient) (primary key)
   • name
   • DOB (date of birth)
3. visits:
   • id
   • id_medic
   • id_patient

I would like to find out the average age of patients for each specialty.

SELECT specialty, AVG(year(curdate()) - year(patients.DOB))
FROM medics, patients, visits 
WHERE medics.IDM = visits.medics GROUP by specialty;

The query above shows me on each line the average of all patients.

Answer 1

Try it:

SELECT
Speciality,
ROUND(AVG(YEAR(NOW())-YEAR(DOB)),0) AS Years
FROM visits

INNER JOIN medics
ON visits.IdMedic = medics.Id

INNER JOIN patients
ON visits.IdPatient = patients.Id

GROUP BY Speciality

My tables:

CREATE TABLE `visits` (
  `Id` int(11) NOT NULL,
  `IdMedic` int(11) DEFAULT NULL,
  `IdPatient` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `patients` (
  `Id` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `medics` (
  `Id` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Speciality` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;