Select with subquery transforming into column

Question

Select with subquery transforming into column

Navigation

#1 by (3 votes)

1

Well, I did not know how to put the title ...

Anyway, I have the table tblnotadisciplina (following image) that will store students' notes of a certain period. These grades have an evaluation type (1-Work, 2-Test). I want to make a query where I get the student's names (using join, of course), note 1 (Work) and note 2 (proof), example

Name nota1 nota2
Student 10 10

Thank you in advance!

mysql

asked by anonymous 01.02.2017 / 01:38

1 answer

Format date on page load Run project in netbeans [closed]

score 3 · Accepted Answer

You can do a conditional sum with if :

SELECT
   nomealuno
   SUM(IF(idavaliacao=1,nota,0)) AS trabalho,
   SUM(IF(idavaliacao=2,nota,0)) AS prova
FROM
   tblaluno a LEFT JOIN tblnotadisciplina b ON a.idmatricula = b.idmatricula 
GROUP BY
   tblaluno.idmatricula

I'm assuming that the grouping is by registration, but it's easy for you to adjust to the field you want. The logic is the same, just adjust the fields according to your reality.