Consulta com vários group by

[JeanGomesP 0]

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.

 

[Motta 645]

Percebam, o que eu quero é as ultimas 3 linhas em apenas uma.

 

 

 

pelo que entendi você fazer uma "pivot query" , pesquise por isto e por

CASE , GROUP_CONCAT etc

[JeanGomesP 0]

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?

[Motta 645]

http://forum.imasters.com.br/topic/450676-resultado-da-consulta-em-linhas/?p=1782024&hl=case%20&fromsearch=1#entry1782024

 

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.

[giesta 29]

Motta, mysql nao tem pivot por padrao mesmo nao

[JeanGomesP 0]

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?

[JeanGomesP 0]

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.

[lokaodomau 61]

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:

http://sqlfiddle.com/#!9/d6d0e/2/0

[Motta 645]

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.

[JeanGomesP 0]

Motta, também faço assim.

lokaodomau cheguei a seguinte consulta:

SELECT `t`.* 
FROM (SELECT `vi`.`data_agendado`, `vi`.`turno`, `vi`.`tipo_agendamento`, 
SUM(CASE p.id_tipo WHEN 2 THEN 1 WHEN p.id_tipo<>2 THEN 0 END) AS `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.

[lokaodomau 61]

Checou todos os JOIN se não está duplicando algo?

[JeanGomesP 0]

Vou revisar os JOIN novamente, realizei a consulta apenas com os estritamente necessários e importantes para o resultado.

[giesta 29]

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

[JeanGomesP 0]

Como ficaria o GROUP BY?

Achei que sabia SQL. :(

[Motta 645]

Para conferir faço os passos

 

Um select "aberto" para um caso

select *
from ....
where cliente = 123

Um agrupado

select cliente ,sum( case ....
from ....
where cliente = 12
group by cliente

fica mais fácil checar

[JeanGomesP 0]

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.