Vários counts em uma consulta

[degui]

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 são 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 então 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

Edited February 20, 2012 by degui

[degui]

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

[Denis Courcy]

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

Procure aqui no forum os artigos relacionados com o assunto "pivot table". Já expliquei isto algumas vezes e farei novamente após você ter pesquisado e não entendido alguma parte.

[degui]

dei uma olhada aqui no fórum, realmente tem alguns posts sobre pivot table, a maioria apontando para um post onde você (Denis Cousy) respondeu com uma profedure... até entedi um pouco o que ele faz, mas não consegui adequar ao meu caso.

vi que tinha alguém com um problema parecido, so que o dele era fornecedores... ele disse que resolveu com sua dica. Infelizmente eu não consegui...

com o codigo abaixo, consegui retornar aproximadamente o que preciso... a questão é que repete o nome do aluno várias vezes (a quatidade de disciplina em que ele tem falta)...

se precisar posso enviar o codigo sql para criar a tabela ai com alguns valores

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

[degui]

andei pesquisando, em outras linguagens tem o transform e pivot... mas não consegui ainda usar no mysql, continuo tentando

[Denis Courcy]

Oi 'degui',

Montei, em minha base de testes, a estrutura de dados que você passou no primeiro post deste tópico.

Elaborei a consulta abaixo para refletir os resultados que você está querendo:

select a.aluno, d.disciplina, count(f.cod_falta) 
from tb_alunos a
inner join tb_turmas t ON t.cod_turma=a.cod_turma
inner join tb_disc_prof_turma dpf ON dpf.cod_turma = t.cod_turma
inner join tb_disciplinas d ON d.cod_disciplina = dpf.cod_disciplina
inner join tb_aulas au ON au.cod_disc_prof_turma = dpf.cod_disc_prof_turma
inner join tb_faltas f ON f.cod_aula = au.cod_aula
group by a.aluno, d.disciplina;

Apliquei a estrutura acima a procedure de pivot table que desenvolvi e disponibilizei aqui:

-- pivotwizard(P_Row_Field, P_Column_Field, P_Value, P_From,P_Where)
call bancodeteste.pivotwizard('a.aluno', 'd.disciplina', 'IF(NOT f.cod_falta IS NULL, 1, 0)', 
                      'tb_alunos a
            inner join tb_turmas t ON t.cod_turma=a.cod_turma
            inner join tb_disc_prof_turma dpf ON dpf.cod_turma = t.cod_turma
            inner join tb_disciplinas d ON d.cod_disciplina = dpf.cod_disciplina
            inner join tb_aulas au ON au.cod_disc_prof_turma = dpf.cod_disc_prof_turma
            inner join tb_faltas f ON f.cod_aula = au.cod_aula', 
                      '1=1');

Veja se funciona.

[degui]

vou testar e posto aqui o resultado...

so uma observacao, as faltas devem ser contadas e agrupada por cod_disc_prof_turma..

por exemplo, o mesmo professores leciona diferentes materias para uma turma...

então, se eu contar as faltas por disciplina, ou por professor, ficará errado.

então tem que contar a falta para aquele relacionamento de disc_prof_turma

obrigado, vou testar desse jeito ai, depois posto o resultado aqui

[Denis Courcy]

vou testar e posto aqui o resultado...

so uma observacao, as faltas devem ser contadas e agrupada por cod_disc_prof_turma..

por exemplo, o mesmo professores leciona diferentes materias para uma turma...

então, se eu contar as faltas por disciplina, ou por professor, ficará errado.

então tem que contar a falta para aquele relacionamento de disc_prof_turma

obrigado, vou testar desse jeito ai, depois posto o resultado aqui

Observe o modelo que você postou. Eu o segui plenamente.

Pelo que entendi de seu problema, não importa qual professor que leciona a disciplina. Importa que, quando o aluno falta ele falta a uma disciplina.

Um aluno (tb_alunos) pertence a uma turma (tb_alunos->tb_turmas) uma turma possui varias disciplinas (tb_turmas->tb_disc_prof_turma->tb_disciplinas). Uma aula (tb_disc_prof_turma->tb_aulas) pode ter presença ou falta (tb_disc_prof_turma->tb_aulas->tb_faltas).

Outro ponto: No parâmetro P_Value da procedure, onde está 'IF(NOT f.cod_falta IS NULL, 1, 0)' passe para

'IF(f.cod_falta IS NULL, 0, 1)', para facilitar o entendimento da lógica.

[degui]

Denis Courcy,

Primeiramente muito obrigado pela sua colaboração e sua vontade em ajudar

Tentei executar, disse que procedure não exisita... então fui la em sua procedure, copiei e tentei executar... ficou carregando... carregando... e não executou... não sei se peguei o código correto, poderia postá-lo aqui, por favor...

acredito que a ordem correta criar primeiro a procedure e depois executar esse código que você me mandou acima... obrigado!

[Denis Courcy]

Denis Courcy,

Primeiramente muito obrigado pela sua colaboração e sua vontade em ajudar

Tentei executar, disse que procedure não exisita... então fui la em sua procedure, copiei e tentei executar... ficou carregando... carregando... e não executou... não sei se peguei o código correto, poderia postá-lo aqui, por favor...

acredito que a ordem correta criar primeiro a procedure e depois executar esse código que você me mandou acima... obrigado!

Antes de postar novamente a procedure, por favor execute o primeiro código que passei. Você falou que ficou rodando, demorando e não apresentando o resultado. Teste o select que passei para afastarmos o problema de table scan em suas tabelas devido a falta de algum índice, já que existem várias tabelas envolvidas em joins.

[degui]

Denis Courcy, mais uma vez obrigado, infelizmente não consegui

quando executo a procedure fica rodando rodando e trava o mysql...

acho que vou pegar o resultado do banco e tentar organizar o que preciso com arrays no php...

se eu converter o resultado em uma array, depois acredito que posso criar um foreach e com os valores montar de acordo a minha necessidade

obrigado pela boa vontade em ajudar

abraço