Duvida com subselect + group by

[felipeiw]

To precisando montar um sql que traga o codigo, nome do cliente, total em vendas e total em trocas dos pedidos com data FATURA is null

tabela PEDIDOS tem o codigo, tabela PEDIDOSITENS sao os itens vendidos e CLIENTES tabela de clientes

dessa maneira da erro Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

uso FB 2.0

SELECT A.CODIGO, C.NOME
(SELECT SUM(E.QNT * E.PRECO) FROM PEDIDOSITENS E WHERE A.NUMERO = E.NUMERO AND E.TIPO = 'VENDA') AS VENDAS,
(SELECT SUM(F.QNT * F.PRECO) FROM PEDIDOSITENS F WHERE A.NUMERO = F.NUMERO AND F.TIPO = 'TROCA') AS TROCAS
FROM PEDIDOS A, CLIENTES C
WHERE A.FATURA IS NULL
GROUP BY A.CODIGO, C.NOME

Alguma sujestão por favor, travei nesse sql 

Obrigado

[Rho Kurcinae]

O erro ocorre porque as colunas "VENDAS" e "TROCAS" não estão especificadas "no GROUP BY" e, como existe uma cláusula "GROUP BY", estas não estão sendo buscadas a partir de uma Função Agregadora (COUNT, SUM, AVG, MAX, MIN e LIST).

Sim, tu estás utilizando SUM, ali, mas apenas nos "SELECT de dentro". O "SELECT de fora" não irá enxergá-los, só irá enxergar os resultados que eles retornam.

Também esqueceste de ligar as tabelas "PEDIDOS" e "CLIENTES".
 

[felipeiw]

Obrigado amigo pela resposta, 

Mais não consegui montar esse sql, mesmo com sua explicação VENDAS E TROCAS é como vc disse do select de dentro e resultados de um SUM, não deu certo colocando-as no group by, quanto as tabelas PEDIDOS e CLIENTES ja corrigi.

Consegue por favor se possivel claro, monta-la ?

Abs

[Rho Kurcinae]

Fiz para Oracle, creio que seja parecido para Firebird 2.0.

SELECT ped.codigo                          codigo
     , cli.nome                            nome
     , SUM (peditven.qnt * peditven.preco) vendas
     , SUM (pedittro.qnt * pedittro.preco) trocas
  FROM pedidos      ped
  JOIN pedidositens peditven ON ((peditven.numero = ped.numero) AND
                                 (peditven.tipo   = 'VENDA'))
  JOIN pedidositens pedittro ON ((pedittro.numero = ped.numero) AND
                                 (pedittro.tipo   = 'TROCA'))
  JOIN clientes     cli      ON (<ligar com a tabela pedidos>)
 WHERE ped.fatura IS NULL
 GROUP BY ped.codigo
        , cli.nome;

 

[datilas]

Em 04/10/2020 at 17:31, felipeiw disse:

To precisando montar um sql que traga o codigo, nome do cliente, total em vendas e total em trocas dos pedidos com data FATURA is null

tabela PEDIDOS tem o codigo, tabela PEDIDOSITENS sao os itens vendidos e CLIENTES tabela de clientes

dessa maneira da erro Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

uso FB 2.0

SELECT A.CODIGO, C.NOME
(SELECT SUM(E.QNT * E.PRECO) FROM PEDIDOSITENS E WHERE A.NUMERO = E.NUMERO AND E.TIPO = 'VENDA') AS VENDAS,
(SELECT SUM(F.QNT * F.PRECO) FROM PEDIDOSITENS F WHERE A.NUMERO = F.NUMERO AND F.TIPO = 'TROCA') AS TROCAS
FROM PEDIDOS A, CLIENTES C
WHERE A.FATURA IS NULL
GROUP BY A.CODIGO, C.NOME

Alguma sujestão por favor, travei nesse sql 

Obrigado

tenta assim:

SELECT CODIGO,NOME,SUM(VENDAS) AS VENDAS,SUM(TROCAS) AS TROCAS FROM (
SELECT A.CODIGO, C.NOME
(SELECT SUM(E.QNT * E.PRECO) FROM PEDIDOSITENS E WHERE A.NUMERO = E.NUMERO AND E.TIPO = 'VENDA') AS VENDAS,
(SELECT SUM(F.QNT * F.PRECO) FROM PEDIDOSITENS F WHERE A.NUMERO = F.NUMERO AND F.TIPO = 'TROCA') AS TROCAS
FROM PEDIDOS A, CLIENTES C
WHERE A.FATURA IS NULL
)
GROUP BY CODIGO,NOME

precisaria de uma base para testar

Editado 6 Outubro, 2020 por datilas

[felipeiw]

Obrigado Rho pela resposta

porem se nao tem troca, nao traz nada, gostaria que trouxe o valor da venda e o valor da troca zerado.

abs

[Rho Kurcinae]

Bastaria apenas trocar JOIN por LEFT JOIN, ficando:

   SELECT ped.codigo                          codigo
        , cli.nome                            nome
        , SUM (peditven.qnt * peditven.preco) vendas
        , SUM (pedittro.qnt * pedittro.preco) trocas
     FROM pedidos      ped
LEFT JOIN pedidositens peditven ON ((peditven.numero = ped.numero) AND
                                     (peditven.tipo   = 'VENDA'))
LEFT JOIN pedidositens pedittro ON ((pedittro.numero = ped.numero) AND
                                     (pedittro.tipo   = 'TROCA'))
     JOIN clientes     cli      ON (<ligar com a tabela pedidos>)
    WHERE ped.fatura IS NULL
    GROUP BY ped.codigo
            , cli.nome;

 

Neste caso, as colunas "VENDAS" e "TROCAS" serão NULL quando não houverem valores nas tabelas filhas.

Caso não queiras NULL, poderás tratá-lo através das Funções COALESCE/NVL.

 

Editado 6 Outubro, 2020 por Rho Kurcinae

[felipeiw]

Rho ficou top, agora se não for pedir muito, se for possivel, como posso montar outro com a mesma estrutura, porem listando codigo e nome do produto ? tabela produtos (codprod) e na tabela pedidositens (codprod)

mt obrigado