[Resolvido] - Consulta Agrupada / referencia cruzada

[kleberalmeida]

Olá

Tenho uma tabela chamada “valores”

cliente | Data |Valor
01 | 01/01/2015 | 12,90
02 | 02/02/2015 | 10,00

• Eu gostaria que na consulta do SQL, me mostrasse um relatório assim:

Cliente |Jan | Fev | Marc |
01 |12,90 |0,00 | 0,00 |
02 | 0,00 |10,00 | 0,00 |

Dai ele somar e montar esse relatorio agrupado…Como faz isso?

Aguardo,

[buhlerax (Alexandre Pereira Bühler)]

Quem me conhece sabe que não gosto de dar resposta prontas.
Então vai algo que pode ajudar se você procurar mais coisas no manual do mysql.
Faça

CREATE TABLE IF NOT EXISTS teste (
idteste int(11) NOT NULL AUTO_INCREMENT,
cliente varchar(45) DEFAULT NULL,
data date DEFAULT NULL,
valor decimal(10,2) DEFAULT NULL,
PRIMARY KEY (idteste)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

depois

INSERT INTO teste (idteste, cliente, data, valor) VALUES
(1, ‘joao’, ‘2015-01-15’, ‘12.00’),
(2, ‘pedro’, ‘2015-01-16’, ‘13.00’),
(3, ‘sebastiao’, ‘2015-02-15’, ‘14.00’),
(4, ‘rodrigo’, ‘2015-02-15’, ‘13.00’),
(5, ‘joao’, ‘2015-02-15’, ‘14.00’),
(6, ‘joao’, ‘2015-01-15’, ‘100.00’);

realize o select:

select cliente, data,
sum(if(month(data) = 1, valor,0 )) janeiro,
sum(if(month(data) = 2, valor,0 )) fevereiro,
sum(if(month(data) = 3, valor,0 )) março,
sum(if(month(data) = 4, valor,0 )) abril,
sum(if(month(data) = 5, valor,0 )) maio,
sum(if(month(data) = 6, valor,0 )) junho,
sum(if(month(data) = 7, valor,0 )) julho,
sum(if(month(data) = 8, valor,0 )) agosto,
sum(if(month(data) = 9, valor,0 )) setembro,
sum(if(month(data) = 10, valor,0 )) outubro,
sum(if(month(data) = 11, valor,0 )) novembro,
sum(if(month(data) = 12, valor,0 )) dezembro
from teste
group by cliente

resultado (ele perde a tabulação quando colo aqui):

±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| cliente | data | janeiro | fevereiro | maro | abril | maio | junho | julho | agosto | setembro | outubro | novembro | dezembro |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| joao | 2015-01-15 | 112.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| pedro | 2015-01-16 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| rodrigo | 2015-02-15 | 0.00 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| sebastiao | 2015-02-15 | 0.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+

já tem a chamada com valores somando por dia agrupado por cliente.
Para somar o total no final falta o manual do mysql ou o tio google.
Boa sorte.

PS: se for usar mês abreviado tipo JAN, FEV lembre-se que SET é palavara reservada. Irá dar erro.

[willian_fernando (Willian Fernando Padilha)]

PS: se for usar mês abreviado tipo JAN, FEV lembre-se que SET é palavara reservada. Irá dar erro.

Teria que usar assim SET

[system]

Acho melhor usar PIVOT do SQL, dá uma pesquisada que vai funcionar legal.

Dá até para fazer ele montar as colunas dinamicamente.

[ticelso (Celso R.)]

http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
http://www.devmedia.com.br/utilizando-pivot-table/4565

[buhlerax (Alexandre Pereira Bühler)]

O exemplo que dei já não é um pivot?

[system]

O exemplo que dei já não é um pivot?

É uma forma de montar sim porém dá para fazer com colunas variáveis.

[buhlerax (Alexandre Pereira Bühler)]

Isto é vero.

[kleberalmeida]

Quem me conhece sabe que não gosto de dar resposta prontas.
Então vai algo que pode ajudar se você procurar mais coisas no manual do mysql.
Faça

CREATE TABLE IF NOT EXISTS teste (
idteste int(11) NOT NULL AUTO_INCREMENT,
cliente varchar(45) DEFAULT NULL,
data date DEFAULT NULL,
valor decimal(10,2) DEFAULT NULL,
PRIMARY KEY (idteste)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

depois

INSERT INTO teste (idteste, cliente, data, valor) VALUES
(1, ‘joao’, ‘2015-01-15’, ‘12.00’),
(2, ‘pedro’, ‘2015-01-16’, ‘13.00’),
(3, ‘sebastiao’, ‘2015-02-15’, ‘14.00’),
(4, ‘rodrigo’, ‘2015-02-15’, ‘13.00’),
(5, ‘joao’, ‘2015-02-15’, ‘14.00’),
(6, ‘joao’, ‘2015-01-15’, ‘100.00’);

realize o select:

select cliente, data,
sum(if(month(data) = 1, valor,0 )) janeiro,
sum(if(month(data) = 2, valor,0 )) fevereiro,
sum(if(month(data) = 3, valor,0 )) março,
sum(if(month(data) = 4, valor,0 )) abril,
sum(if(month(data) = 5, valor,0 )) maio,
sum(if(month(data) = 6, valor,0 )) junho,
sum(if(month(data) = 7, valor,0 )) julho,
sum(if(month(data) = 8, valor,0 )) agosto,
sum(if(month(data) = 9, valor,0 )) setembro,
sum(if(month(data) = 10, valor,0 )) outubro,
sum(if(month(data) = 11, valor,0 )) novembro,
sum(if(month(data) = 12, valor,0 )) dezembro
from teste
group by cliente

resultado (ele perde a tabulação quando colo aqui):

±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| cliente | data | janeiro | fevereiro | maro | abril | maio | junho | julho | agosto | setembro | outubro | novembro | dezembro |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| joao | 2015-01-15 | 112.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| pedro | 2015-01-16 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| rodrigo | 2015-02-15 | 0.00 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| sebastiao | 2015-02-15 | 0.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+

já tem a chamada com valores somando por dia agrupado por cliente.
Para somar o total no final falta o manual do mysql ou o tio google.
Boa sorte.

PS: se for usar mês abreviado tipo JAN, FEV lembre-se que SET é palavara reservada. Irá dar erro.

Eu tentei colocar dessa forma no SC e deu um erro…
Como posso usar esse SQL no SC??

Aguardo

[kleberalmeida]

O exemplo que dei já não é um pivot?

É uma forma de montar sim porém dá para fazer com colunas variáveis.

PIVOT no Mysql não existe amigão…

[buhlerax (Alexandre Pereira Bühler)]

Se a referência é ao comando PIVOT realmente não existe. Somente em outros banco de dados.
Agora a “técnica PIVOT”, no mysql, existe sim.
E melhor você, AMIGÃO, explicar para todo mundo no google http://lmgtfy.com/?q=mysql+table+pivot que isto não existe.

[Jailton]

Quem me conhece sabe que não gosto de dar resposta prontas.
Então vai algo que pode ajudar se você procurar mais coisas no manual do mysql.
Faça

CREATE TABLE IF NOT EXISTS teste (
idteste int(11) NOT NULL AUTO_INCREMENT,
cliente varchar(45) DEFAULT NULL,
data date DEFAULT NULL,
valor decimal(10,2) DEFAULT NULL,
PRIMARY KEY (idteste)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

depois

INSERT INTO teste (idteste, cliente, data, valor) VALUES
(1, ‘joao’, ‘2015-01-15’, ‘12.00’),
(2, ‘pedro’, ‘2015-01-16’, ‘13.00’),
(3, ‘sebastiao’, ‘2015-02-15’, ‘14.00’),
(4, ‘rodrigo’, ‘2015-02-15’, ‘13.00’),
(5, ‘joao’, ‘2015-02-15’, ‘14.00’),
(6, ‘joao’, ‘2015-01-15’, ‘100.00’);

realize o select:

select cliente, data,
sum(if(month(data) = 1, valor,0 )) janeiro,
sum(if(month(data) = 2, valor,0 )) fevereiro,
sum(if(month(data) = 3, valor,0 )) março,
sum(if(month(data) = 4, valor,0 )) abril,
sum(if(month(data) = 5, valor,0 )) maio,
sum(if(month(data) = 6, valor,0 )) junho,
sum(if(month(data) = 7, valor,0 )) julho,
sum(if(month(data) = 8, valor,0 )) agosto,
sum(if(month(data) = 9, valor,0 )) setembro,
sum(if(month(data) = 10, valor,0 )) outubro,
sum(if(month(data) = 11, valor,0 )) novembro,
sum(if(month(data) = 12, valor,0 )) dezembro
from teste
group by cliente

resultado (ele perde a tabulação quando colo aqui):

±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| cliente | data | janeiro | fevereiro | maro | abril | maio | junho | julho | agosto | setembro | outubro | novembro | dezembro |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| joao | 2015-01-15 | 112.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| pedro | 2015-01-16 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| rodrigo | 2015-02-15 | 0.00 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| sebastiao | 2015-02-15 | 0.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+

já tem a chamada com valores somando por dia agrupado por cliente.
Para somar o total no final falta o manual do mysql ou o tio google.
Boa sorte.

PS: se for usar mês abreviado tipo JAN, FEV lembre-se que SET é palavara reservada. Irá dar erro.

Este é o modo correto mesmo de se fazer PIVOT, sempre faço assim.

[kleberalmeida]

Quem me conhece sabe que não gosto de dar resposta prontas.
Então vai algo que pode ajudar se você procurar mais coisas no manual do mysql.
Faça

CREATE TABLE IF NOT EXISTS teste (
idteste int(11) NOT NULL AUTO_INCREMENT,
cliente varchar(45) DEFAULT NULL,
data date DEFAULT NULL,
valor decimal(10,2) DEFAULT NULL,
PRIMARY KEY (idteste)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

depois

INSERT INTO teste (idteste, cliente, data, valor) VALUES
(1, ‘joao’, ‘2015-01-15’, ‘12.00’),
(2, ‘pedro’, ‘2015-01-16’, ‘13.00’),
(3, ‘sebastiao’, ‘2015-02-15’, ‘14.00’),
(4, ‘rodrigo’, ‘2015-02-15’, ‘13.00’),
(5, ‘joao’, ‘2015-02-15’, ‘14.00’),
(6, ‘joao’, ‘2015-01-15’, ‘100.00’);

realize o select:

select cliente, data,
sum(if(month(data) = 1, valor,0 )) janeiro,
sum(if(month(data) = 2, valor,0 )) fevereiro,
sum(if(month(data) = 3, valor,0 )) março,
sum(if(month(data) = 4, valor,0 )) abril,
sum(if(month(data) = 5, valor,0 )) maio,
sum(if(month(data) = 6, valor,0 )) junho,
sum(if(month(data) = 7, valor,0 )) julho,
sum(if(month(data) = 8, valor,0 )) agosto,
sum(if(month(data) = 9, valor,0 )) setembro,
sum(if(month(data) = 10, valor,0 )) outubro,
sum(if(month(data) = 11, valor,0 )) novembro,
sum(if(month(data) = 12, valor,0 )) dezembro
from teste
group by cliente

resultado (ele perde a tabulação quando colo aqui):

±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| cliente | data | janeiro | fevereiro | maro | abril | maio | junho | julho | agosto | setembro | outubro | novembro | dezembro |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+
| joao | 2015-01-15 | 112.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| pedro | 2015-01-16 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| rodrigo | 2015-02-15 | 0.00 | 13.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| sebastiao | 2015-02-15 | 0.00 | 14.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
±----------±-----------±--------±----------±-----±------±-----±------±------±-------±---------±--------±---------±---------+

já tem a chamada com valores somando por dia agrupado por cliente.
Para somar o total no final falta o manual do mysql ou o tio google.
Boa sorte.

PS: se for usar mês abreviado tipo JAN, FEV lembre-se que SET é palavara reservada. Irá dar erro.

Este é o modo correto mesmo de se fazer PIVOT, sempre faço assim.

Agora, como usar isso no SC…??

Ja tentei e so da um erro…

Qual a melhor forma de fazer isso no SC?

[kleberalmeida]

Já usei o Exemplo feito pelo nosso amigo… O Alexandre Pereira Bühler e funcionou muito bem no Delphi… Porem no SC tentei usar mas não consegui… Alguem tem uma dica??

[buhlerax (Alexandre Pereira Bühler)]

Vou explicar umas coisa.
Cada compilador ou interpretador tem suas particularidades.
Por isto repito: não adianta comprar o Scriptcase se você não tem paciência para ler o manual da linguagem escolhida ou mesmo da database para fazer o seu software.
Se tivesse ligado o debug do scriptcase teria visto que o mysql retorna um valor nulo nos meses sem lançamento e o php na conexão não está conseguindo interpretar o valor false retornado:

b: select count(*) from teste group by cliente (mysqlt): SELECT cliente, data, sum(if(month(data) = 1,valor,0 )) janeiro as sum_2, sum(if(month(data) = 2,valor,0 )) fevereiro as sum_3, sum(if(month(data) = 3,valor,0 )) março as sum_4, sum(if(month(data) = 4,valor,0 )) abril as sum_5 from teste group by cliente LIMIT 0,12 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘as sum_2, sum(if(month(data) = 2,valor,0 )) fevereiro as sum_3, sum(if(month(dat’ at line 1

        ADOConnection._Execute(SELECT cliente, data, sum(if(month(data) = 1,valor,0 )) janeiro as sum_2, sum(if(month(data) = 2,valor,0 )) fevereiro as sum_3, ..., false) % line 1085, file: adodb.inc.php
     ADOConnection.Execute(SELECT cliente, data, sum(if(month(data) = 1,valor,0 )) janeiro as sum_2, sum(if(month(data) = 2,valor,0 )) fevereiro as sum_3, ..., false) % line  506, file: adodb-mysql.inc.php
  ADODB_mysql.SelectLimit(SELECT cliente, data, sum(if(month(data) = 1,valor,0 )) janeiro as sum_2, sum(if(month(data) = 2,valor,0 )) fevereiro as sum_3, ..., 12, 0) % line  607, file: grid_teste_3_grid.class.php

grid_teste_3_grid.inicializa() % line 90, file: grid_teste_3_grid.class.php
grid_teste_3_grid.monta_grid(0) % line 1611, file: index.php[/b]

[b] Na sintaxe “group by” o comando “sum” retorna null -> do manual do mysql

SUM([DISTINCT] expr)

Returns the sum of expr. [color=green]If the return set has no rows, SUM() returns NULL.[/color] The DISTINCT keyword can be used to sum only the distinct values of expr.

SUM() returns NULL if there were no matching rows. [/b]

[b]Eu também disse neste post:
“Quem me conhece sabe que não gosto de dar resposta prontas.
Então vai algo que pode ajudar se você procurar mais coisas no manual do mysql.”

Logo, não é para colocar o que indiquei como possível solução direto no scriptcase.
É para desenvolver sua solução.
[/b]

O valores nulos estão sendo passados nos meses sem lançamento para janeiro, fevereiro, março…
Logo ele retorna: nenhuma coluna -> If the return set has no rows, SUM() returns NULL.
No mysql ele monta a consulta e mostra na tela.
No delphi os componentes de conexão estão fazendo o tratamento destes meses com null.
Agora no scriptcase não sei se é um problema no ADODB ou no framework do scriptcase em si. Mas ele pega o valor null devolvido como padrão
O importante é fazer o tratamento dos meses nulos dentro do próprio select.

Um tratamento para isto é:

select cliente, data,
sum(if(ifnull(month(data),0) = 1, valor,0 )) as janeiro,
sum(if(ifnull(month(data),0) = 2, valor,0 )) as fevereiro,
sum(if(ifnull(month(data),0) = 3, valor,0 )) as março,
sum(if(ifnull(month(data),0) = 4, valor,0 )) as abril,
sum(if(ifnull(month(data),0) = 5, valor,0 )) as maio,
sum(if(ifnull(month(data),0) = 6, valor,0 )) as junho,
sum(if(ifnull(month(data),0) = 7, valor,0 )) as julho,
sum(if(ifnull(month(data),0) = 8, valor,0 )) as agosto,
sum(if(ifnull(month(data),0) = 9, valor,0 )) as setembro,
sum(if(ifnull(month(data),0) = 10, valor,0 )) as outubro,
sum(if(ifnull(month(data),0) = 11, valor,0 )) as novembro,
sum(if(ifnull(month(data),0) = 12, valor,0 )) as dezembro
from teste
group by cliente

Olha que beleza ficou:

http://www.simaoebuhler.com.br/teste/groupby/grid_teste_1
http://www.simaoebuhler.com.br/teste/groupby/grid_teste_2

[buhlerax (Alexandre Pereira Bühler)]

Kleber,
Deu certo?

[kleberalmeida]

Opa, cara tentei fazer aqui mas deu um erro…
Mas to tentando…

Obrigado,

[buhlerax (Alexandre Pereira Bühler)]

Nem com a correção no select que postei não deu certo?

[kleberalmeida]

Nem com a correção no select que postei não deu certo?

Olá
Amigão, Muito obrigado…
Agora deu certo… Era uma letra que tava dando erro…

[buhlerax (Alexandre Pereira Bühler)]

Beleza, não esqueça de colocar resolvido no tópico