2

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:

enter image description here

And i get:

enter image description here

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?

Thanks!

2
  • I don't see a calification column but I see a qualification one. Which of the 2 is correct? Feb 15, 2014 at 18:41
  • See this question and answer Help with this query. Try to use Option 3. Feb 15, 2014 at 18:45

2 Answers 2

1

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT 
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM 
    pupils                   AS p 
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class' 
WHERE 
    p.level = 1
    AND p.class = 'A' 
    AND p.id_trimester = 1 
GROUP BY 
    p.id_pupil 
  , p.name
  , p.surname ;
0

Quick and dirty fix:

SELECT 
    pupils.id_pupil
  , name
  , surname
  , round(avg(qualifications.calificacion),2) as average
  , (select count(calificacion) from qualifications where id_pupil = pupils.id_pupil and qualifications.id_trimester=incidents.id_trimester and type_qualification='class') 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
WHERE 
    level=1
    and class='A' 
    and id_trimester=1 
GROUP BY id_pupil
3
  • I had to change " round(avg(qualifications.calificacion),2) as average " by (select round(avg(qualifications.calificacion),2) from qualifications where id_pupil = pupils.id_pupil and qualifications.id_trimester=incidents.id_trimester and type_qualification='class') as media because it didn´t find the qualifications.calificacion. Jan 16, 2014 at 11:17
  • However, it doesn´t show the results with all the columns with 0. How can i solve that problem? Jan 16, 2014 at 11:18
  • You could try doing a union, to add back in just those results that are being filtered out
    – dwurf
    Jan 16, 2014 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.