Vários counts em uma consulta

[bichopapo 0]

Ola pessoal,

estou tentando fazer uma consulta em um banco mysql e estou tendo uma dificultado, juntar vários counts,

 

e o seguinte, tenho um banco de faltas de alunos. o aluno recebe a falta por aula, esta aula esta relacionada a uma disciplina, um professor e turma.

 

preciso retornar o nome do aluno e a quantidade de falta que ele tem em cada uma das disciplinas (cod_disc_prof_turma)

 

estou enviando uma imagem, mostrando mais ou menos como sao minhas tabelas. abaixo da linha vermelha, é como eu gostaria que a consulta ao banco retornasse...

 

com esse código eu consegui o subtotal de cada disc_prof_turma, mas, o nome do aluno aparece várias vezes e o total em uma coluna, preciso que apareca apenas uma vez o nome do aluno e varias colunas com o total de falta de cada disc_prof_turma

 

 

a maior confusão pode estar na tabela disc_prof_turma, que é uma relação de professores, com turmas e disciplinas

 

Veja bem, um professor pode dar aulas de duas ou mais disciplinas, para várias turmas ou para uma mesma turma

 

(por exemplo: professor JOÃO dá aula de ALGORITMOS, BANCO DE DADOS e também de SISTEMAS OPERACIONAIS... ele trabalha com essas disciplinas para alunos das turmas 1, 2, 3, 4, 5... portando, o mesmo professor, tem várias disciplinas e várias turmas)

 

Dessa maneira, quando o aluno recebe uma falta, essa falta e cadastrada na tabela faltas, que tem a seguinte estrutura. Lembrando que fiz isso, pois cada falta vem devidamente acompanhada da aula, assim, sei exatamente em que dia o aluno faltou e em que aula ele faltou

 

cod_falta  /  cod_aluno  /    cod_aula
   1               3              2

 

o aluno recebeu uma falta em uma aula (aula 2)

 

essa aula de código 2 está relacionada em outra tabela, a tabela aulas...

cod_aula  /  data_aula    /  cod_disc_prof_turma  
   2            15/2/2012            3                    

 

o cod_disc_prof_turma indica qual o professor, a disciplina e a turma daquela aula... que por sua vez estãoo relacionados da seguinte maneira

 

cod_disc_prof_turma  /  cod_prof     /  cod_disc   / cod_turma
        3                   1               3               5

 

a falta então esta computada para o aluno 3. podemos entao dizer que o aluno 3, da turma 5, tem 1 falta com o professor 1, na disciplina 3, que ele recebeu na aula 2, que aconteceu no dia 15/2/2012

 

 

quanto ao relacionamento, eles so existem na hora da consulta, não sei fazer no phpmyadmin o que eu fazia por exemplo no access... la eu relacionava as tabelas e uma ficava ligada a outra por esse relacionamento.

aqui, o relacionamento existe apenas 'na minha cabeça' e junto essas tabelas no select.

 

veja um que conseguia agora de manha, so acho que ficará muito extenso, pois se uma turma tiver por exemplo 10 disciplinas, terei que repetir o trecho do código 10 vezes...

 

desde já agradeço pela sua ajuda

 

 

SELECT a.cod_aluno, a.aluno, ttt. * , zzzz1.tt_disc_1, zzzz2.tt_disc_2,zzzz1.cod_aluno
FROM tb_alunos a
LEFT JOIN (

SELECT t.cod_aluno, COUNT( t.cod_falta ) 
FROM tb_faltas t
GROUP BY cod_aluno
) AS ttt ON a.cod_aluno = ttt.cod_aluno

LEFT JOIN (
SELECT COUNT( zzz1.cod_falta ) AS tt_disc_1, zzz1.cod_aula, zzz1.cod_aluno, c.cod_disc_prof_turma
FROM tb_faltas zzz1
INNER JOIN tb_aulas c ON zzz1.cod_aula = c.cod_aula
where c.cod_disc_prof_turma=11
GROUP BY zzz1.cod_aluno, c.cod_disc_prof_turma
) AS zzzz1 ON a.cod_aluno = zzzz1.cod_aluno 


LEFT JOIN (
SELECT COUNT( zzz2.cod_falta ) AS tt_disc_2, zzz2.cod_aula, zzz2.cod_aluno, c.cod_disc_prof_turma
FROM tb_faltas zzz2
INNER JOIN tb_aulas c ON zzz2.cod_aula = c.cod_aula
where c.cod_disc_prof_turma=3
GROUP BY zzz2.cod_aluno, c.cod_disc_prof_turma
) AS zzzz2 ON a.cod_aluno = zzzz2.cod_aluno




WHERE a.cod_turma =2
GROUP BY a.cod_aluno
ORDER BY a.aluno
LIMIT 0 , 100

 

[Motta 645]

Como aluno se relaciona com aula ?!

 

Como aluno se relaciona com turma ?

[bichopapo 0]

desculpe, faltou ali um relacionamento do da tabela alunos com a tabela turmas... quanto ao relacionamento aluno com turma, so existe atraves da tabela faltas... ou seja, a unica coisa que quero saber do aluno e se ele faltou em determinada aula...

 

obrigado!

[Motta 645]

Creio que seria melhor você publicar o modelo corrigido.

[bichopapo 0]

imagem corrigida... obrigado pela colaboração

[Motta 645]

Faça um join tb_disciplina , tb_alunos , tb_faltas , tb_disc_prof_turma.

 

Para "colunar" a disciplina pode-se usar o case mas aí o número de disciplinas teria de ser previamente conhecido.

[bichopapo 0]

agradeço a ajuda Motta, mas não tenho como prever a quantidade de disciplina

cada turma tem uma quantidade de disciplina diferente.., então a consulta teria que ser dinâmica

 

consegui uma busca com o codigo abaixo que retorna o total de faltas de cada aluno em cada disciplina... so que o nome do aluno aparece na tabela varias vezes (a quantidade de disciplinas em que ele tem falta)... tem como separar isso na hora de exibir no php com o foreach ??

 

SELECT COUNT( b.cod_falta ) AS tot_faltas, a.cod_aluno as cod_alu, a.aluno, b.cod_aluno, b.cod_aula, c.cod_disc_prof_turma, c.cod_aula, ttt. *
FROM tb_alunos a
LEFT JOIN tb_faltas b ON a.cod_aluno = b.cod_aluno
LEFT JOIN tb_aulas c ON b.cod_aula = c.cod_aula
LEFT JOIN (

SELECT t.cod_aluno, COUNT( t.cod_falta )
FROM tb_faltas t
GROUP BY cod_aluno
) AS ttt ON a.cod_aluno = ttt.cod_aluno
WHERE a.cod_turma =2
GROUP BY a.cod_aluno, c.cod_disc_prof_turma
ORDER BY a.aluno
LIMIT 0 , 100

 

Preciso que o php retorne na tela algo parecido com a imagem abaixo... acontece que tem turma que as disciplinas são diferentes... e o banco tem que ver isso no relacionamento de prof_disc_turma

[Motta 645]

Não conheço PHP, não sei.

Para fazer no SQL usaria o CASE como já disse.

Uma solução é usar um gerador de relatório que faça a pivotização (tipo o Crystal).

[bichopapo 0]

andei pesquisando e cheguei realmente ao pivot... mas não consegui ainda usar no mysql, continuo tentando