SUM em subqueryes

[paulojuchem 20]

I CANNOT LIVE LIKE THIS ANYMORE

 

 
SELECT
f.nome AS entregador ,
/* */   (SELECT
SUM(h.valor*h.quantidade)
FROM pedidos_tem_produtos h
WHERE h.fk_pedidos=p.id
) AS total  ,
COUNT(p.id) AS quantidade
FROM pedidos p
INNER JOIN entregadores e ON p.fk_entregadores=e.id
INNER JOIN funcionarios f ON e.fk_funcionarios=f.id
WHERE p.status!=4 AND p.ts_registro
BETWEEN '2013-01-29 01:31:57' AND '2013-01-29 02:57:03'
GROUP BY p.fk_entregadores

ali onde tem /* */ eu quero colocar um SUM, mas nao funciona, eu preciso somar essa subquery, por causa do group by, tem como somar uma subquery? ela so retorna 1 campo, pelo que pesquisei nao da certo, se for, vou odiar a oracle por ter comprado mysql pelo resto da vida, ta parado no tempo essa )(*&^%$E#W@#$%^&* c@##@2 g!@#9@!#@# de mer!@#$%!@#

[Prog 183]

Não entendi... Você já tem um SUM dentro da subquery, não entendi como quer usar este outro SUM.

 

Seu GROUP BY deve conter campos que fazem parte do seu SELECT.

[Motta 645]

SELECT f.nome AS entregador , SUM(h.valor*h.quantidade) AS total , COUNT(p.id) AS quantidade

FROM pedidos p

INNER JOIN entregadores e ON p.fk_entregadores=e.id

INNER JOIN funcionarios f ON e.fk_funcionarios=f.id

INNER JOIN pedidos_tem_produtos h ON h.fk_pedidos=f.id

WHERE p.status!=4

AND p.ts_registro BETWEEN '2013-01-29 01:31:57' AND '2013-01-29 02:57:03'

GROUP BY p.fk_entregadores

 

[Prog 183]

Sem conhecer o modelo, parece que há algo mais que não encaixa...

 

Aguardando retorno do autor.

[paulojuchem 20]

Ola, a solucao do mota nao funciona pois sao retornadas linhas duplicadas pela query, para cada produto do pedido a row do pedido retorna 1 vez, 2 produtos = 2 vezes e assim por diante, so pode retornar 1x.

Ola pog, sim ,ja tem um SUM na subquery, e ele retorna os dados do somatorio da subquery, ate ai blz, mas o problema eh que o group by agrupa varios resultados da subquery, e esses eu tenho que somar tbm, da forma como esta ele retorna apenas o resultado da 1 vez que a subquery retorna, sem o group by, daqui a pouco eu posto um pedaco do modelo com exatamente o que eu quero fazer.

[Motta 645]

Qual o relacionamento entre as tabelas ?

[paulojuchem 20]

Bom, apenas para esclarecer a situacao, estou desenvolvendo um sistema de tele-entregas para uma pizzaria, o mesmo ja esta no ar e como eu havia combinado com o contratante, tudo sera desenvolvido aos poucos, ate agora, a pessoa liga, o numero cai no sistema, sao puxados os dados deste numero(de telefone), caso existam, e em seguida o pedido eh criado e impresso na cozinha.

Agora, estou fazendo a parte do caixa que vai controlar quem entregou oque e quanto dinheiro este entregador possui, bem como as entregas canceladas e pendentes.

Vamos ao que interessa, segue a estrutura das tabelas que fazem parte do problema apresentado:

 

CREATE TABLE IF NOT EXISTS `caixas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts_abertura` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts_encerramento` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `fk_funcionarios` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_caixas_funcionarios` (`fk_funcionarios`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
CREATE TABLE IF NOT EXISTS `entregadores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `senha` int(4) NOT NULL,
  `fk_funcionarios` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fk_funcionarios` (`fk_funcionarios`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
CREATE TABLE IF NOT EXISTS `funcionarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(50) NOT NULL,
  `dt_nascimento` date NOT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
CREATE TABLE IF NOT EXISTS `pedidos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts_registro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fk_clientes` int(11) NOT NULL,
  `fk_enderecos` int(11) DEFAULT NULL,
  `fk_funcionarios` int(11) NOT NULL,
  `status` int(1) DEFAULT '0',
  `troco` double DEFAULT NULL,
  `pago` tinyint(1) DEFAULT '0',
  `obs` varchar(500) DEFAULT NULL,
  `fk_entregadores` int(11) DEFAULT NULL,
  `fk_telefones` int(11) NOT NULL,
  `ts_delivery` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ts_to_delivery` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cancel_reason` varchar(200) DEFAULT NULL,
  `cancelation_user` int(11) DEFAULT NULL,
  `fk_forma_pagamento` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_pedidos_clientes` (`fk_clientes`),
  KEY `fk_pedidos_enderecos` (`fk_enderecos`),
  KEY `fk_pedidos_funcionarios` (`fk_funcionarios`),
  KEY `fk_pedidos_entregadores` (`fk_entregadores`),
  KEY `fk_pedidos_telefones` (`fk_telefones`),
  KEY `fk_pedidos_usuarios_cancelation` (`cancelation_user`),
  KEY `fk_pedidos_forma_pagamento` (`fk_forma_pagamento`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=160 ;
DROP TRIGGER IF EXISTS `tg_pedidos_bu`;
DELIMITER //
CREATE TRIGGER `tg_pedidos_bu` BEFORE UPDATE ON `pedidos`
 FOR EACH ROW BEGIN
IF(OLD.status > 0 AND NEW.status=0) THEN
INSERT INTO backup_pedidos_retornos (fk_pedidos , fk_entregadores) VALUES (OLD.id , OLD.fk_entregadores);
SET NEW.ts_to_delivery = 0;
ELSEIF(OLD.STATUS=0 AND NEW.status=1) THEN
SET NEW.ts_to_delivery = CURRENT_TIMESTAMP;
ELSEIF(OLD.status=1 AND NEW.status=2) THEN
SET NEW.ts_delivery = CURRENT_TIMESTAMP;
END IF;
END
//
DELIMITER ;
CREATE TABLE IF NOT EXISTS `pedidos_tem_produtos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quantidade` int(5) NOT NULL DEFAULT '1',
  `fk_produtos` int(11) NOT NULL,
  `fk_pedidos` int(11) NOT NULL,
  `valor` double DEFAULT NULL,
  `obs` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pedidos_tem_produtos_prod` (`fk_produtos`),
  KEY `fk_pedidos_tem_produtos_ped` (`fk_pedidos`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=362 ;

 

Basicamente, eu tenho os status dos pedidos da seguinte forma :

0 = preparando

1 = entregando

2 = entregue

4 = cancelado

 

O que eu quero fazer?

eu quero fazer um grid que mostre para mim, agrupando por cada entregador, o nome dele, quantas entregas ele fez durante o periodo de determinado caixa e quanto dinheiro isso rendeu.

ex:

| entregador | qnt | total |

+-------------+--------+--------+

| juca | 14 | 973.00|

| luiz | 10 | 887.00 |

| caixa | 27 |2200.00|

|abertas | 10 |1248.00|

+-------------+-------+---------+

|total | 61 | xxxxxx |

+------------+--------+---------+

|canceladas | 5 | 245.00|

+------------+--------+--------+

 

quero fazer um grid parecido com o acima, tem que ser algo pratico e objetivo(para o usuario).

entao, agora vamos ao problema

A selecao de pedidos ocorre de acordo com a data de inicio e encerramento do caixa, entao eu seleciono o caixa, vejo o inicio e o fim e seleciono os pedidos de acordo com esses dados, nenhuma fk esta segurando caixas com pedidos, a data de registro do pedido eh utilizada como referencia.

 

SELECT f.nome AS entregador , COUNT(p.id) AS quantidade FROM pedidos p INNER JOIN entregadores e ON p.fk_entregadores=e.id INNER JOIN funcionarios f ON e.fk_funcionarios=f.id WHERE p.ts_registro BETWEEN 'x' AND 'y' GROUP BY p.fk_entregadores;

da forma acima eu ja tenho o nome do entregador e a quantidade de entregas que cada entregador fez no periodo dado, agora eu quero somar tambem quanto de dinheiro que o somatorio de todas as entregas que cada um fez deu. O valor de cada entrega esta em uma tabela chamada pedidos_tem_produtos, ela possui os campos fk_pedidos, fk_produtos , quantidade e valor. Logo, para obter o valor total de cada item, basta fazer quantidade*valor.

OBS.: Se eu colocar um inner join na query acima com a tabela pedidos_tem_produtos, supondo que um pedido tenha 3 produtos, sao retornadas 3 rows, ou seja o COUNT(p.id) vai retornar 3 vezes para esse pedido, o que nao pode!

 

 

OBS2: eu sou burro, se eu usar COUNT(DISTINCT(p.id)) funciona

 

como eu ja escrevi tudo isso, estou com do de apagar =p, pode marcar como resolvido, mas ainda tem um problema, nao tem como usar SUM no resultado de uma subquery

 

eu ja posto como que ficou, ta parecido com o que o mota escreveu, o que eu tentei fazer no comeco tbm

here we go

 

(SELECT f.nome AS entregador , SUM(h.quantidade*h.valor) AS total  , COUNT(DISTINCT(p.id)) AS quantidade FROM pedidos p INNER JOIN entregadores e ON p.fk_entregadores=e.id INNER JOIN funcionarios f ON e.fk_funcionarios=f.id INNER JOIN pedidos_tem_produtos h ON h.fk_pedidos=p.id WHERE p.status!=4 AND p.ts_registro BETWEEN '2013-01-29 01:31:57' AND '2013-01-29 02:57:03' GROUP BY p.fk_entregadores) UNION ALL (SELECT 'em aberto' ,  SUM(h.valor*h.quantidade) AS total  , COUNT(DISTINCT(p.id)) AS quantidade FROM pedidos p INNER JOIN pedidos_tem_produtos h ON h.fk_pedidos=p.id WHERE p.status=0 AND p.ts_registro BETWEEN '2013-01-29 01:31:57' AND '2013-01-29 02:39:48' ) UNION ALL (SELECT 'cancelados' , SUM(h.valor*h.quantidade) AS total  , COUNT(DISTINCT(p.id)) AS quantidade FROM pedidos p INNER JOIN pedidos_tem_produtos h ON h.fk_pedidos=p.id WHERE p.status=4 AND p.ts_registro BETWEEN '2013-01-29 01:31:57' AND '2013-01-29 02:39:48')

 

btw, prog, essa parada de o group by ter que ser um campo do select eu nao sabia, achava que podia ser qualquer coisa que estivesse no meio da query

[Motta 645]

Veja se ajuda.