A average Mark overall for all of one student subjects Lets say  i have a student table , a enlistment table that shows SubjectId and Results for each student  how would i get the overall average mark for a student that has more than 3 subjects
Create table  a_Student(
StudentId int(5) Not null,
StudentForename varchar (30) Not null,
StudentSurname varchar (45) Not null, 
PRIMARY KEY(StudentId)
);
Create table a_Enlistment(
StudentId int(5) Not null,
SubjectId char(6) Not null,
results char (2),
primary key (SubjectId,StudentId),
foreign key (SubjectId) references a_Subject(SubjectId),
foreign key (StudentId) references a_Student(StudentId)
);


insert into a_Student (StudentId,StudentForename,StudentSurname)
values (&quot;33447&quot;,&quot;Alan&quot;,&quot;Burns&quot;),
(&quot;33672&quot;,&quot;Mark&quot;,&quot;Harrison&quot;,); 


 Insert into a_Enlistment (StudentId,SubjectId,Results)
values  
        (&quot;33447&quot;,&quot;TMP243&quot;,&quot;78&quot;),
         (&quot;33447&quot;,&quot;AWD169&quot;,&quot;45&quot;),
         (&quot;33447&quot;,&quot;IIA887&quot;,&quot;48&quot;),
        (&quot;33672&quot;,&quot;TMP243&quot;,&quot;57&quot;),
       (&quot;33672&quot;,&quot;AWD169&quot;,&quot;67&quot;),
      (&quot;33672&quot;,&quot;IIA887&quot;,&quot;48&quot;),

So i want lets say Alans overall mark for his 3 subjects  how would i do that?
Any help is welcomed thanks a lot
you can use a subquery to get the result for average and the number of subjects


Create table  a_Student(
StudentId int(5) Not null,
StudentForename varchar (30) Not null,
StudentSurname varchar (45) Not null, 
PRIMARY KEY(StudentId)
);




Create table a_Enlistment(
StudentId int(5) Not null,
SubjectId char(6) Not null,
results char (2),
primary key (SubjectId,StudentId),
#foreign key (SubjectId) references a_Subject(SubjectId),
foreign key (StudentId) references a_Student(StudentId)
);




insert into a_Student (StudentId,StudentForename,StudentSurname)
values (&quot;33447&quot;,&quot;Alan&quot;,&quot;Burns&quot;),
(&quot;33672&quot;,&quot;Mark&quot;,&quot;Harrison&quot;); 




 Insert into a_Enlistment (StudentId,SubjectId,Results)
values  
        (&quot;33447&quot;,&quot;TMP243&quot;,&quot;78&quot;),
         (&quot;33447&quot;,&quot;AWD169&quot;,&quot;45&quot;),
         (&quot;33447&quot;,&quot;IIA887&quot;,&quot;48&quot;),
        (&quot;33672&quot;,&quot;TMP243&quot;,&quot;57&quot;),
       (&quot;33672&quot;,&quot;AWD169&quot;,&quot;67&quot;),
      (&quot;33672&quot;,&quot;IIA887&quot;,&quot;48&quot;),
      (&quot;33672&quot;,&quot;test&quot;,&quot;52&quot;)


✓




SELECT s.*
,avgresults
FROM a_Student s INNER JOIN (SELECT
StudentId,
AVG(Results) avgresults,
COUNT(*) countr FROM a_Enlistment
GROUP BY StudentId) e ON s.StudentId = e.StudentId
WHERE countr &gt; 3


StudentId | StudentForename | StudentSurname | avgresults
--------: | :-------------- | :------------- | ---------:
    33672 | Mark            | Harrison       |         56


db&lt;&gt;fiddle here