Select with subquery turning into column

Asked

Viewed 385 times

1

Well, I didn’t know how to put the title...

Anyway, I have the table tblnotadiscipline (follows picture) which will store student grades for a certain period. These grades have a kind of evaluation (1-Job, 2-Test). I want to perform a query where I receive the student’s names (using Join, of course), grade 1 (Work) and grade 2 (proof), example

Name Nota1 nota2
Aluno1 10 10

Thanks in advance!

tblnotadisciplina

mysql

  • That would be: http://answall.com/questions/7999/converter-linha-para-column?

1 answer

3


You can make 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 guessing the grouping is by registration, but it’s simple for you to adjust to whatever field you want. The logic is the same, just adjust the fields according to your reality.

  • Man, thank you so much! Fit like a glove.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.