HELP sum group

Setembro 30, 2008

DESEJO SABER A SOMA DOS VALORES DE CADA PEDIDO E CONFRONTAR COM A SOMA DOS VALORES DOS RECIBOS(ITENS) PARA CADA UM DESTES PEDIDOS. MINHA QUERY (UNIFICADA) FUNCIONA quando TENH OUM PEDIDO E UM RECIBO, PORÉM ESTA DANDO ALGUM ERRO NO GROUP BY quando TENHO MAIS DE UM RECIBO PARA O MESMO PEDIDO. se julgarem pertinente a tabela recibo, favor pedir.

erro: soma errado os valores do pedido

SE ALGUÉM SE PRONTIFICAR A AJUDAR SEREI MUITO GRATO.

CODE

###############################################

# assim funciona separadamente

SELECT

p.id_pedido,

SUM(p.tx_assessoria+p.tx_agendamento+

p.tx_consular+

p.tx_urgencia+

p.tx_extra)AS V_Pedido

FROM pedido p

GROUP BY p.id_pedido

SELECT

r.id_pedido,

SUM(r.tx_assessoria+

r.tx_agendamento+

r.tx_consular+

r.tx_urgencia+

r.tx_extra)AS V_Recibo

FROM recibo_item r

GROUP BY r.id_pedido

###############################################

# gostaria assim: tentativa unificada

SELECT

p.id_pedido,

(SELECT nome FROM passageiro pa WHERE (pa.id_passageiro=p.id_passageiro)) AS Passageiro,

(SELECT passaporte FROM passageiro pa WHERE (pa.id_passageiro=p.id_passageiro)) AS Passaporte,

IFNULL(

SUM(p.tx_assessoria+p.tx_agendamento+

p.tx_consular+

p.tx_urgencia+

p.tx_extra),0)AS V_Pedido,

IFNULL(

SUM(r.tx_assessoria+

r.tx_agendamento+

r.tx_consular+

r.tx_urgencia+

r.tx_extra),0)AS V_Recibo,

IFNULL(

SUM(r.tx_assessoria+

r.tx_agendamento+

r.tx_consular+

r.tx_urgencia+

r.tx_extra

-(p.tx_assessoria+

p.tx_agendamento+

p.tx_consular+

p.tx_urgencia+

p.tx_extra)),

-(p.tx_assessoria+

p.tx_agendamento+

p.tx_consular+

p.tx_urgencia+

p.tx_extra)) AS Diferenca

FROM pedido p

INNER JOIN recibo_item r ON ((p.id_pedido = r.id_pedido AND

(r.tx_assessoria+

r.tx_agendamento+

r.tx_consular+

r.tx_urgencia+

r.tx_extra

-(p.tx_assessoria+

p.tx_agendamento+

p.tx_consular+

p.tx_urgencia+

p.tx_extra) != 0))

)

GROUP BY r.id_pedido, p.id_pedido

CODE

CREATE TABLE passageiro (

id_passageiro int(11) NOT NULL auto_increment,

id_main int(11) default NULL,

nome varchar(50) NOT NULL,

endereco varchar(50) default NULL,

complemento varchar(30) default NULL,

cep varchar(10) default NULL,

email varchar(50) default NULL,

data_nasc date default NULL,

cpf varchar(15) default NULL,

passaporte varchar(15) default NULL,

validade_passaporte date default NULL,

fone_residencial varchar(15) default NULL,

fone_celular_1 varchar(15) default NULL,

fone_celular_2 varchar(15) default NULL,

fone_comercial_1 varchar(15) default NULL,

ramal_1 varchar(5) default NULL,

fone_comercial_2 varchar(15) default NULL,

ramal_2 varchar(5) default NULL,

local_emissao varchar(35) default NULL,

sexo varchar(10) NOT NULL,

ocupacao varchar(50) default NULL,

local_ocupacao varchar(50) default NULL,

PRIMARY KEY (id_passageiro),

KEY nome (nome,data_nasc,passaporte)

) ENGINE=InnoDB AUTO_INCREMENT=336 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;

CREATE TABLE pedido (

id_pedido int(11) NOT NULL auto_increment,

id_destino int(11) NOT NULL,

id_passageiro int(11) NOT NULL,

id_entidade int(11) NOT NULL,

id_finalidade int(11) NOT NULL,

id_status int(11) NOT NULL,

tx_agendamento_num varchar(30) default NULL,

tx_assessoria float(15,2) default NULL,

tx_agendamento float(15,2) default NULL,

tx_consular float(15,2) default NULL,

tx_urgencia float(15,2) default NULL,

tx_extra float(15,2) default NULL,

dt_pedido date NOT NULL,

hr_pedido varchar(5) NOT NULL,

dt_viagem date default NULL,

dt_entrevista date default NULL,

dt_emissao date default NULL,

dt_expiracao date default NULL,

obs longtext,

obs_ent longtext,

PRIMARY KEY (id_pedido),

KEY idx_FK3 (id_destino),

KEY idx_FK4 (id_status),

KEY idx_FK6 (id_entidade),

KEY idx_FK7 (id_passageiro),

KEY idx_FK9 (id_finalidade)

) ENGINE=InnoDB AUTO_INCREMENT=225 DEFAULT CHARSET=latin1 AUTO_INCREMENT=20;

CREATE TABLE recibo_item (

id_recibo_item int(11) NOT NULL auto_increment,

id_recibo int(11) NOT NULL,

id_pedido int(11) NOT NULL,

tx_assessoria float(15,2) NOT NULL default '0.00',

tx_agendamento float(15,2) NOT NULL default '0.00',

tx_consular float(15,2) NOT NULL default '0.00',

tx_urgencia float(15,2) NOT NULL default '0.00',

tx_extra float(15,2) NOT NULL default '0.00',

PRIMARY KEY (id_recibo_item),

KEY idx_FK13 (id_recibo),

KEY idx_FK14 (id_pedido)

) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=latin1 AUTO_INCREMENT=20;

INSERT INTO passageiro VALUES (1, 5, 'PASSAGEIRO 1', 'RUA DOS CATAVENTOS, 123', 'APTO 345', '90.810-897', 'leandro@gmail.com', '1981-12-21', '744.663.482-99', 'AB123', '2010-12-21', '51 3384-2302', '51 8144-2344', '', '', '', '', '', 'RS-PORTO ALEGRE', 'M', 'FUNCIONARIO', 'DMAEST'),

(2, 5, 'PASSAGEIRO 2', '', '', '', '', '0000-00-00', '', 'AC123', '2010-12-21', '', '', '', '', '', '', '', '', 'M', '', '');

INSERT INTO pedido (id_pedido, id_destino, id_passageiro, id_entidade, id_finalidade, id_status, tx_agendamento_num, tx_assessoria, tx_agendamento, tx_consular, tx_urgencia, tx_extra, dt_pedido, hr_pedido, dt_viagem, dt_entrevista, dt_emissao, dt_expiracao, obs, obs_ent) VALUES

(1, 1, 1, 1, 1, 6, '', 150.00, 100.00, 150.00, 100.00, 100.00, '2008-07-02', '21:58', '0000-00-00', '0000-00-00', '2008-07-22', '2008-07-01', 'teste','teste'),

(2, 2, 2, 2, 2, 6, '', 500.00, 50.00, 50.00, 50.00, 500.00, '2008-07-02', '22:13', '0000-00-00', '0000-00-00', '0000-00-00', '0000-00-00', 'teste', 'teste'),

(3, 2, 2, 2, 2, 6, '', 50.00, 0.00, 50.00, 0.00, 0.00, '2008-07-02', '22:17', '0000-00-00', '0000-00-00', '0000-00-00', '0000-00-00', 'teste', 'teste');

INSERT INTO recibo_item (id_recibo_item, id_recibo,id_pedido,tx_assessoria,tx_agendamento,tx_consular,tx_urgencia,tx_extr

a) VALUES

(1, 1, 1, 100.00, 100.00, 100.00, 100.00, 100.00),

(2, 2, 1, 50.00, 0.00, 50.00, 0.00, 0.00),

(3, 2, 2, 50.00, 0.00, 50.00, 0.00, 0.00),

(4, 3, 3, 50.00, 50.00, 50.00, 50.00, 50.00),

(5, 4, 3, 10.00, 0.00, 10.00, 0.00, 0.00);

paulobergo · Outubro 1, 2008

a) Primeiro, vamos simplificar... se você organizar melhor as queries,

fica mais fácil... veja um exemplo...

Digamos que você tem a seguinte query:

select nf_numero, nf_pedido, nf_data, nf_cfop, nf_valor
from `acme`.`notasfiscais` as nfs
where nf_data between "2008/08/01" and "2008/08/01"
  and substring(nf_cfop, 1, 1) in ("5", "6", "7")
order by nf_numero;

nf_numero  nf_pedido  nf_data      nf_cfop  nv_valor
  002040     000404     01/08/2008   5102          800
  002041     000406     01/08/2008   6102          100
  002042     000407     01/08/2008   5102          200
  002043     000405     01/08/2008   5102          300
  002044     000000     01/08/2008   5102          150

* obtenho todas as notas fiscais, com o valor total de cada uma... e ainda esta outra query:

select numero, sum(quantidade*valor_unit) as soma
from `acme`.`pedidos` as peds
where ped_data_fat between "2008/08/01" and "2008/08/10"
group by numero

numero   soma
000404    800
000405    300
000406    100
000407    150

* obtenho todos os pedidos faturados, com o valor total de cada um deles. B) Agora eu quero ligar o resultado da segunda query com a primeira, para checar, por exemplo, se as notas faturadas tem o mesmo valor dos pedidos ou se tem algum pedido que não foi atendido totalmente...

select nf_numero, nf_pedido, nf_data, nf_cfop, nf_valor, soma as valor_pedido
from 
  (select nf_numero, nf_pedido, nf_data, nf_cfop, nf_valor, valor_pedido
   from `acme`.`notasfiscais` as nfs
   where nf_data between "2008/08/01" and "2008/08/01"
     and substring(nf_cfop, 1, 1) in ("5", "6", "7")
   order by nf_numero
  ) as querya
left outer join
  (select numero, sum(quantidade*valor_unit) as soma
   from `acme`.`pedidos` as peds
   where ped_data_fat between "2008/08/01" and "2008/08/01"
   group by numero
  ) as queryb
on (querya.nf_pedido=queryb.numero);

nf_numero  nf_pedido  nf_data      nf_cfop  nv_valor  valor_pedido
  002040     000404     01/08/2008   5102          800           800
  002041     000406     01/08/2008   6102          100           100
  002042     000407     01/08/2008   5102          200           150
  002043     000405     01/08/2008   5102          300           300
  002044     000000     01/08/2008   5102          150             0

c) Note que a nota fiscal 002044 não tem pedido... ou seja, foi faturada diretamente sem que houvesse uma pré-venda... O "left outer join" fez, então, que todas as notas aparecessem, havendo ou não pedidos para elas... d) Se você quiser consultar apenas as notas fiscais cujo valor coincide com o do pedido, acrescentaria apenas

where (querya.nf_valor=queryb.soma)

e) E apenas as notas com valor diferente do pedido:

where (not (querya.nf_valor=queryb.soma)) or
            (queryb.soma is null)

Ok?

Espero ter contribuido...

Entrar

HELP sum group

Pergunta

Guest --leandro --

Link para o comentário

Compartilhar em outros sites

1 resposta a esta questão

Posts Recomendados

paulobergo

Link para o comentário

Compartilhar em outros sites

Estatísticas dos Fóruns

Navegação

Atividades