I have these tables:
pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) qualifications: (id_qualification, qualification, date, time, subject, id_pupil, id_user, id_trimester, type_qualification)
and i did this query:
SELECT pupils.id_pupil , name , surname , round(avg(qualifications.calificacion),2) as average , count(qualifications.calificacion) as count , COALESCE(sum(type_incident='miss' and level=1 and class='A' and id_trimester=1),0) as misses , COALESCE(sum(type_incident='delay' and level=1 and class='A' and id_trimester=1),0) as delays , COALESCE(sum(type_incident='attitude' and level=1 and class='A' and id_trimester=1),0) as attitude , COALESCE(sum(type_incident='miss_justif' and level=1 and class='A' and id_trimester=1),0) as misses_justificadas FROM pupils left join incidents ON incidents.id_pupil=pupils.id_pupil left join qualifications ON qualifications.id_pupil=pupils.id_pupil WHERE level=1 and class='A' and id_trimester=1 and type_qualification='class' and qualifications.id_trimester=incidents.id_trimester GROUP BY id_pupil
But the problem is, the correct result i would have to get it would be:
And i get:
The problem is that multiplicate the number of class qualifications and the misses (17x6(2 misses, 1 delay, 1 attitude and 2 misses_just)=102)) and i don´t want that. I want the correct information showed in the first picture.
Would you like to help me, please?