1
I have the following Mysql query, the results are shown as in the image below, I would like the values of the columns not to be repeated Aluno
and NotaGeral
based on the column Usuario
which is a unique value. it is not enough to ask not to repeat the values Alunos
or NotaGeral
as there may be equal grades of different users and equal student names, but being different users. Any suggestions?
select avg(IFNULL(fraction, 0))*10 as Media, gg.finalgrade as NotaGeral, u.firstname as Aluno, u.username as Usuario, u.lastname as siem, qas.userid, u.department as Turma, u.institution as Escola, qc.name as cat
FROM mdl_question_attempt_steps qas
inner join mdl_user u on u.id=qas.userid
INNER JOIN mdl_question_attempts qa ON qa.id=qas.questionattemptid
INNER JOIN mdl_question q On q.id=qa.questionid
inner join mdl_grade_grades gg on gg.userid=u.id
INNER JOIN mdl_grade_items gi ON gi.`id` = gg.`itemid`
INNER JOIN mdl_quiz_slots qs ON qs.questionid=qa.questionid
Inner Join mdl_quiz quiz ON quiz.id=qs.quizid
inner join mdl_question_categories qc ON qc.id=q.category
where substring(u.department,2,1) = 4
AND itemtype = "mod"
AND itemmodule = "quiz"
AND finalgrade != "NULL"
and substr(quiz.name,-1)=1
and substr(quiz.ano,1,4)=2018
and u.lastname=213
and qas.state!="todo"
and qas.state!="complete"
group by turma, q.category, u.id
ORDER BY turma ASC, `Aluno` ASC, cat DESC
Good morning bro, the names are repeating because the column "Cat" has more than one value for the same "User". What really you want?
– Wellington Araujo
@Wellingtonaraujo, Good morning! then, it is because of aesthetics, because it is strange to repeat the values unnecessarily, for example, in the case of the column
Cat
all right, they should be displayed, because they are different values, but in the case of columns,Alunos
andNotaGeral
it’s not nice to repeat equal values for the same user, you know? Thanks for asking– Miguel Silva
but in a select this is normal... but in case you want to show it on a system then you will have to use a FOR or WHILE
– Wellington Araujo
Got it, that’s where I get lost, rsrs
– Miguel Silva
there is a way to do with "PIVOT Table", I have never done it in Mysql only in SQL, in Mysql is different, if you want to try access the link below. But I still prefer to mount it in the application. https://multitibusiness.wordpress.com/2014/02/14/pivot-table-mysql/
– Wellington Araujo
I’ll take a look, thanks @Wellingtonaraujo
– Miguel Silva
I’ll post my comment as an answer, ok, then you score please.
– Wellington Araujo