Group data from a table

Asked

Viewed 178 times

1

I have the following sql statement:

Select 

Notas.NotaID,
Materias.MateriaNome, 
Alunos.AlunoNome,  
Alunos.AlunoID,
   max(case when NotaBimestre = 1 then Nota else null end) as 1bim,
   max(case when NotaBimestre = 2 then Nota else null end) as 2bim,
   max(case when NotaBimestre = 3 then Nota else null end) as 3bim,
   max(case when NotaBimestre = 4 then Nota else null end) as 4bim

From ((notas 
INNER JOIN Materias 
   ON Notas.MateriaID = Materias.MateriaID) 
INNER JOIN Alunos
   ON Notas.AlunoID = Alunos.AlunoID)      

group by (NotaID)

Upshot:

inserir a descrição da imagem aqui

How do I stay in that pattern:

ID | ALUNO    | DISCIPLINA           | 1BIM| 2BIM | 3BIM | 4BIM
16 | NATANAEL | BANCO DE DADOS       | 8   |      |      | 
17 | GUSTAVO  | PROGRAMAÇÃO PARA WEB | 9.5 | 8    | 8.5  | 10
php mysql

  • Which DBMS you are using?

  • You will need to consult the grade table 1 time for each quarter, not go through it just once as it is in your query. I recommend using Subqueries for such

  • Take the "note id" from GROUP BY.

  • @Motta will not help because he needs to check the two months. In case he will have to make a table with the values and then perform a PIVOT

2 answers

2


whereas his SGBD be the SQL Server you can gather the data in a subquery and carry out the PIVOT on top of that data:

SELECT a.alunonome AS aluno,
       m.materianome AS disciplina,
       y.bim1,
       y.bim2,
       y.bim3,
       y.bim4,
  FROM (
    SELECT n.materiaid,
           n.alunoid,
           'bim' + CAST(n.notabimestre AS VARCHAR) as semestre,
           n.nota
      FROM notas n
  ) x
  PIVOT (AVG(x.nota) FOR semestre IN (bim1, bim2, bim3, bim4)) y
  INNER JOIN materias m ON m.materiaid = y.materiaid
  INNER JOIN alunos a ON a.alunoid = y.alunoid

If using the MySQL, you can group using CASE and adjusting the GROUP BY:

SELECT a.alunonome AS aluno,
       m.materianome AS disciplina,
       AVG(CASE n.notabimestre
             WHEN 1 THEN n.nota
           END) AS bim1,
       AVG(CASE n.notabimestre
             WHEN 2 THEN n.nota
           END) AS bim2,
       AVG(CASE n.notabimestre
             WHEN 3 THEN n.nota
           END) AS bim3,
       AVG(CASE n.notabimestre
             WHEN 4 THEN n.nota
           END) AS bim4
  FROM nota n
 INNER JOIN materias m ON m.materiaid = n.materiaid
 INNER JOIN alunos a ON a.alunoid = n.alunoid
 GROUP BY n.materiaid, n.alunoid, a.alunonome, m.materianome

AVG

Returns the Average value of expr... If there are no matching Rows, AVG() Returns NULL.

In free translation:

Returns the mean value of the expression... If there are no lines, AVG() will return NULL.

Observing: I noticed that you used the function MAXto get the notes but I believe that the ideal in your case is to use the AVG, since what matters is the average.

  • 1

    Very good Sorack! Helped me here

  • @Smokerohden displays. If possible add which DBMS tags in the question, please

  • If I want to show the banknote ID for the two-month period?

  • @Smokerohden Only if you concatenate, since there are 1 to several

1

Your SELECT cannot start in the Notes table, you have to start in students, and do a SUB-SELECT for every two months or so:

 SELECT b.MateriaNome, a.AlunoNome, a.AlunoID,
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 1 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '1bim',
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 2 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '2bim',
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 3 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '3bim',
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 4 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '4bim',
    FROM Alunos a
    INNER JOIN Materias b
    GROUP BY a.AlunoID, b.MateriaId;

Browser other questions tagged php mysql

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