Relatorio com porcentagem de participação

[Bruno Conte 4]

Boa noite, o cliente me pediu um ultimo relatorio onde eles querem saber as salas que mais participaram de coletas por porcentagem.

por exemplo, ele tem 3 salas cadastradas.

uma com 10 participantes, outra com 20 e outra com 100.

na sala de 10 participantes 9 coletaram o que da 90% de participação

na sala de 20 8 coletaram o que da 40% de participação

na de 10 66 coletaram o que da 66% de participação.

O que ele quer e ver as salas que tiveram maior indice de participação e não quem tem mais participantes, agora não sei como montar uma query para esse tipo de relatório.

[Motta 645]

Algo assim ...

select escola.nome , (sum(coleta.quantidade) / sum(participante)) perc

from escola , coleta

where escola.id = coleta.id_escola

group by escola.nome

order by 2 desc

[Bruno Conte 4]

Motta desculpa minha ignorancia mais não sou especialista em banco de dados.

fiz assim so que não deu certo:

SELECT salas.nome, (

SUM( coletas.id_sala ) / SUM( alunos.id_aluno )

)perc
FROM salas, coletas, alunos
WHERE salas.id_sala = coletas.id_sala
AND alunos.id_sala = coletas.id_sala
GROUP BY salas.nome
ORDER BY 2 DESC

Porque eu tenho 2 salas cadastradas e uma sala tem 2 alunos e a outra 1.

uma tinha que dar 100% de participação e a outra 50% e os resultados foram bem errados, alguma coisa fiz errada.

o resultado deu no que tinha que ser 100% deu 1.1429 e no de 50% deu 0.5000

[Motta 645]

Na que .5 bastaria multiplicar por 100.

Na que deu 1.1429 talvez porque existam mais coletas que alunos

Em todo caso publica o modelo e os relacionamentos

[Bruno Conte 4]

Não sei se meu pensamento esta errado o Motta.

Mais por exemplo neste relatorio não me importa a quantidade de vezes que o aluno fez a coleta e sim a quantidade de alunos.

Fiz uma mudança a pedido do cliente que ele informa a quantidade de alunos tem na sala no cadastro da sala e não mais pegando os que estão cadastrados.

SELECT salas.nome, (

SUM( coletas.id_sala ) / salas.qtdalunos

)perc
FROM salas, coletas, alunos
WHERE salas.id_sala = coletas.id_sala
AND alunos.id_sala = coletas.id_sala
GROUP BY salas.nome
ORDER BY 2 DESC

agora a duvida e como fazer para o sum pegar apenas 1 vez o mesmo aluno.

na tabela coletas tenho o id_aluno e id_sala

[Motta 645]

Tente

SELECT salas.nome, (count( coletas.id_sala ) / salas.qtdalunos) perc
FROM salas, coletas, alunos
WHERE salas.id_sala = coletas.id_sala
AND alunos.id_sala = coletas.id_sala
GROUP BY salas.nome , salas.qtdalunos
ORDER BY 2 DESC

[Bruno Conte 4]

Motta usando count ele soma, por exemplo a quantidade de id_sala, pois quando o id_sala e id_aluno tiver mais de 1 ele precisa somar apenas 1 vez, mais o calculo fico correto agora.

[Bruno Conte 4]

Motta resolvi assim:

SELECT salas.nome, (

COUNT( DISTINCT coletas.id_sala ) / salas.qtdalunos

)perc
FROM salas, coletas, alunos
WHERE salas.id_sala = coletas.id_sala
AND alunos.id_sala = coletas.id_sala
GROUP BY salas.nome, salas.qtdalunos
ORDER BY 2 DESC

[Bruno Conte 4]

Motta hoje fazendo alguns testes depois do cliente ter falado, vi que não esta ficando correto.

SELECT escolas.nome, escolas.id_escola, coletas.data, alunos.id_aluno, SUM( DISTINCT coletas.id_aluno ) totalalunos, (

SUM( DISTINCT coletas.id_aluno ) / SUM( escolas.qtdalunos )

)perc

FROM escolas, coletas, alunos

WHERE escolas.id_escola = coletas.id_escola and coletas.id_aluno = alunos.id_aluno and coletas.data BETWEEN '2016-01-01' AND '2016-01-31'

GROUP BY escolas.nome

ORDER BY perc DESC

coloquei um:

SUM( DISTINCT coletas.id_aluno ) totalalunos

e quando tinha que ter o resultado 1 ele esta dando 5, se você entrar na tabela coletas só tem 1 e mesmo se somar tudo da 4 e não sei porque esta dando 5 o resultado, assim nunca dará certo o percentual.

[Motta 645]

Publica as tabelas e os relacionamentos.

[Bruno Conte 4]

create table escolas (

id_escola int(11) not null auto_increment,

nome varchar(255) default null,

cpfcnpj varchar(255) default null,

nomeresp varchar(255) default null,

cep varchar(255) default null,

endereco varchar(255) default null,

numero varchar(255) default null,

complemento varchar(255) default null,

bairro varchar(255) default null,

cidade varchar(255) default null,

estado varchar(255) default null,

pais varchar(255) default null,

telefone varchar(255) default null,

celular varchar(255) default null,

email varchar(255) default null,

diretor varchar(255) default null,

vicediretor varchar(255) default null,

coordenador varchar(255) default null,

qtdalunos varchar(255) default null,

anotacoes varchar(255) default null,

primary key(id_escola)

);

create table alunos (

id_aluno int(11) not null auto_increment,

id_escola int(11) default null,

id_sala int(11) default null,

nome varchar(255) default null,

cep varchar(255) default null,

endereco varchar(255) default null,

numero varchar(255) default null,

complemento varchar(255) default null,

bairro varchar(255) default null,

cidade varchar(255) default null,

estado varchar(255) default null,

telefone varchar(255) default null,

celular varchar(255) default null,

email varchar(255) default null,

responsavel varchar(255) default null,

telefoneresp varchar(255) default null,

celularresp varchar(255) default null,

anotacoes varchar(255) default null,

primary key(id_aluno)

);

create table coletas (

id_coleta int(11) not null auto_increment,

id_escola int(11) default null,

id_sala int(11) default null,

id_aluno int(11) default null,

qtd varchar(255) default null,

data date default null,

primary key(id_coleta)

);

na tabela escolas tem uma coluna qtdalunos que é a quantidade de alunos da escola.

[Motta 645]

--coletas por escola/aluno

select id_escola,id_aluno

from coletas

group by id_escola,id_aluno

--coletas por escola

select id_escola,count(*) qtd

from (select id_escola,id_aluno

from coletas

group by id_escola,id_aluno ) al_coleta

group by id_escola

--escola x coletas

select escola.nome , escola.qtdalunos , coletas_escolas.qtd

from escola,( select id_escola,count(*) qtd

from (select id_escola,id_aluno

from coletas

group by id_escola,id_aluno ) al_coleta

group by id_escola) coletas_escolas

where coletas_escolas.id_escola = escola.id_escola

[Bruno Conte 4]

tentei usar da o seguinte erro:

#1248 - Every derived table must have its own alias

[Motta 645]

#1248 - Every derived table must have its own alias

Toda tabela derivada deve ter seu próprio alias

Corrigi acima

[Bruno Conte 4]

Correto deu certo:

SELECT escolas.nome, escolas.qtdalunos, coletas_escolas.qtd

FROM escolas, (

SELECT id_escola, COUNT( * ) qtd

FROM (

SELECT id_escola, id_aluno

FROM coletas

GROUP BY id_escola, id_aluno

)al_coleta

GROUP BY id_escola

)coletas_escolas

WHERE coletas_escolas.id_escola = escolas.id_escola

Mais como ficaria a parte do percentual?

[Motta 645]

SELECT 100 * (escolas.nome, coletas_escolas/escolas.qtdalunos) perc ....

[Bruno Conte 4]

Já sofri aqui Motta, banco de dados não é minha praia, não consigo fazer o do percentual nem com sua ajuda.

[Motta 645]

SELECT escolas.nome, 100 * (coletas_escolas.qtd/escolas.qtdalunos) perc

FROM escolas, (

SELECT id_escola, COUNT( * ) qtd

FROM (

SELECT id_escola, id_aluno

FROM coletas

GROUP BY id_escola, id_aluno

)al_coleta

GROUP BY id_escola

)coletas_escolas

WHERE coletas_escolas.id_escola = escolas.id_escola

[Bruno Conte 4]

Motta estou lendo e tentando intender a logica.

deu o seguinte erro:

#1054 - Unknown column 'coletas_escolas' in 'field list'

pelo que intendi o coletas_escolas e o resultado aqui:

SELECT id_escola, COUNT( * ) qtd

FROM (

SELECT id_escola, id_aluno

FROM coletas

GROUP BY id_escola, id_aluno

)al_coleta

GROUP BY id_escola

)coletas_escolas

[Motta 645]

corrigi , veja lá agora