Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Prezados, estou realizando uma consulta para trazer dados referentes a uma agenda de visitas, mas estou com dúvidas para fazer um agrupamento.
Segue o código SQL para darem uma olhada:
SELECT vi.data_agendado, vi.turno, vi.tipo_agendamento,
IF(p.id_tipo=2,COUNT(p.id_tipo),0) AS categoriaA,
IF(p.id_tipo=4,COUNT(p.id_tipo),0) AS categoriaB,
IF(p.id_tipo=1,COUNT(p.id_tipo),0) AS categoriaC,
IF(p.id_tipo=3,COUNT(p.id_tipo),0) AS categoriaD,u.nome AS tec_agenda,u.id_usuario, u.color, u2.nome AS motorista, car.modelo AS transporte, v.id_vistoria, codigo_rj,
IF(m.id_municipio=68,ce.bairro_distrito,m.municipio) AS localidade,
IF( c.tipo = 1,c.nome,razao_social) AS nome_cliente, cc.nome AS contato
FROM tbl_vistoria_item AS vi
LEFT JOIN tbl_acl_usuario AS u ON u.id_usuario=vi.id_tecnico_agenda
LEFT JOIN tbl_acl_usuario AS u2 ON u2.id_usuario=vi.id_motorista
LEFT JOIN tbl_veiculo AS car ON car.id_veiculo=vi.id_transporte
INNER JOIN tbl_produto AS p ON p.id_produto=vi.id_produto
INNER JOIN tbl_vistoria AS v ON v.id_vistoria=vi.id_vistoria
INNER JOIN tbl_cliente_endereco AS ce ON ce.id_endereco=v.id_endereco
INNER JOIN tbl_municipio AS m ON m.id_municipio = ce.id_municipio
INNER JOIN tbl_cliente AS c ON c.id_cliente=v.id_cliente
INNER JOIN tbl_cliente_contato AS cc ON cc.id_endereco=v.id_endereco
WHERE (v.situacao = 'Em atendimento') AND (vi.data_agendado IS NOT NULL) AND (data_agendado = '2015-05-06' )
GROUP BY vi.id_vistoria, vi.data_agendado,p.id_tipo
ORDER BY vi.data_agendado ASC, localidade ASC, tec_agenda ASC
Segue resultado obtido atualmente:
https://drive.google.com/file/d/0BwiE_a-qekS5N25aem1fVlJ6RTg/view?usp=sharing
Percebam, o que eu quero é as ultimas 3 linhas em apenas uma.
É o mesmo tecnico que vai realizar uma vistoria no mesmo cliente, na mesma data, mas tenho que mostrar a quantidade de equipamentos de cada categoria que ele vai vistoriar. Ignorem dados relativos a carro e motorista.
Se eu agrupar por vi.id_tecnico_agenda a visita do técnico vem em uma mesma linha, pensei em fazer subconsulta para trazer os totais.
Se precisarem de mais esclarecimento sobre o modelo eu informo.
Poxa, interessante esse esquema de "pivot query", não conhecia ele, nem a função GROUP_CONCAT, só não estou conseguindo aplicar aqui. Vou continuar tentando.
Pode me ajudar a clarear o uso do pivot?
Veja se ajuda.
Até onde saiba o MySql não tem o comando PIVOT como no Oracle então tem de ter solução alternativa.
Motta, mysql nao tem pivot por padrao mesmo nao
Sim, pelo que vi aqui, no mysql você usa o conceito, utilizando outros recursos do banco.
Motta, estou quase chegando na solução, mas não consigo o resultado que preciso, entendi a consulta do exemplo que vc passou, mas ao adaptar a ideia para minha situação tenho que usar o COUNTno lugar se SUM, não estou tendo o resultado certo. Alguma luz?
Estou pensando em fazer uma tabela auxiliar para armazenar os dados relacionados a agenda, essa query tá me deixando doido.
Se a visita do cliente só tem uma categoria de equipamento a quantidade é mostrada corretamente, mas quando tem mais de um tipo para o mesmo técnico a contagem não bate.
Acredito que seja o tipo que esteja replicando os registros, como é a mesma vistoria, acredito que seja o mesmo cliente, você pode remover o id_tipo do GROUP BY e utilizar SUM(1) para contar, desta forma, somaria 1 para cada linha, depois utilizar o GROUP_CONCAT() para exibir todos os tipos.
Veja um exemplo:
Para fazer sqls deste tipo costumo fazer
Uma versão "aberta" para conferencia
A versão agregadora
Vou fazendo para casos isolados até fechar
Mas tem horas que complica mesmo.
Motta, também faço assim.
lokaodomau cheguei a seguinte consulta:
SELECT `t`.*
FROM (SELECT `vi`.`data_agendado`, `vi`.`turno`, `vi`.`tipo_agendamento`,categoriaA,
SUM(CASE p.id_tipo WHEN 4 THEN 1 WHEN p.id_tipo<>4 THEN 0 END) AS categoriaB,
SUM(CASE p.id_tipo WHEN 1 THEN 1 WHEN p.id_tipo<>1 THEN 0 END) AS categoriaC,
SUM(CASE p.id_tipo WHEN 3 THEN 1 WHEN p.id_tipo<>3 THEN 0 END) AS categoriaD,
u.nome AS tec_agenda, u.id_usuario, u.color, v.id_vistoria, codigo_rj,
IF(m.id_municipio=68,ce.bairro_distrito,m.municipio) AS localidade,
IF( c.tipo = 1,c.nome,razao_social) AS nome_cliente, cc.nome AS contato
FROM tbl_produto AS p
INNER JOIN tbl_vistoria_item AS vi ON p.id_produto=vi.id_produto
INNER JOIN tbl_acl_usuario AS u ON u.id_usuario=vi.id_tecnico_agenda
INNER JOIN tbl_vistoria AS v ON v.id_vistoria=vi.id_vistoria
INNER JOIN tbl_cliente_endereco AS ce ON ce.id_endereco=v.id_endereco
INNER JOIN tbl_municipio AS m ON m.id_municipio = ce.id_municipio
INNER JOIN tbl_cliente AS c ON c.id_cliente=v.id_cliente
INNER JOIN tbl_cliente_contato AS cc ON cc.id_endereco=v.id_endereco
WHERE (v.situacao = 'Em atendimento') AND (vi.data_agendado IS NOT NULL) AND (data_agendado = '2015-06-01')
GROUP BY vi.id_vistoria, vi.data_agendado, id_tecnico_agenda
ORDER BY vi.data_agendado ASC, localidade ASC, tec_agenda ASC) AS t
Esses "CASES" penso que posso transformar em IFs para simplificar.
O agrupamento tá ok mas a contagem não.
Mas ainda me retorna resultados duplicados.
É a mesma vistoria para o mesmo cliente, a vistoria (tbl_vistoria) possui itens (tbl_vistoria_item), que são equipamentos (tbl_produto) que possuem uma classificação (id_tipo).
Tenho um caso aqui que era para exibir a qtd 5 de uma classificação para um técnico, mas tá duplicando para 10.
Tem vistoria com itens de um mesmo grupo que são vistoriados por um técnico na data x e por outro na data y.
Checou todos os JOIN se não está duplicando algo?
Vou revisar os JOIN novamente, realizei a consulta apenas com os estritamente necessários e importantes para o resultado.
esse tipo de consulta eu sempre faco em duas etapas
a primeira converte os dados("gira") e a segunda soma
exemplo:
tipo | valor
a | 10
a | 20
b |30
c |50
ai vem alguem e pede um relatorio assim: a | b | c
entao seria:
select sum(a) a, sum(b) b, sum© from
(
select if(tipo = a ,valor,0) a , if(tipo = b,valor,0) b, if(tipo=c,valor,0) c from tabela
)z
Como ficaria o GROUP BY?
Achei que sabia SQL. :(
Para conferir faço os passos
Um select "aberto" para um caso
select *select cliente ,sum( case ....
from ....
where cliente = 12
group by cliente
fica mais fácil checar
Ressuscitando.
Só para dizer que a consulta foi resolvida, com a ajuda de vocês, muito obrigado.
Tinha um join duplicando a bagaça.
Agora, quando eu tiver tempo, tentar otimizar, reduzir um pouco de JOINS.
pelo que entendi você fazer uma "pivot query" , pesquise por isto e por
CASE , GROUP_CONCAT etc