Mais Vendidos entre Pedidos e Vendas

Delphi

05/03/2006

tenho duas tabelas Pedidos e Vendas:

SELECT CodiProd, SUM(QuanProd) as Qtda from NotaItem
where Data between ´01.01.2005´ and ´03.03.2006´
Group by CodiProd
Order By Qtda Desc, CodiProd

Retorna:
1 - 49
2 - 21
6 - 10
3 - 5
4 - 5
5 - 1

SELECT CodiProd, SUM(QuanProd) as Qtda from OrcaItem
where Data between ´01.01.2005´ and ´03.03.2006´
Group by CodiProd
order by Qtda Desc, CodiProd

Retorna:
2 - 10
6 - 10
1 - 2

SELECT CodiProd, SUM(QuanProd) as Qtda from NotaItem
where Data between ´01.01.2005´ and ´03.03.2006´
Group by CodiProd
Union All
SELECT CodiProd, SUM(QuanProd) as Qtda from OrcaItem
where Data between ´01.01.2005´ and ´03.03.2006´
Group by CodiProd
order by Qtda Desc, CodiProd

Retorna:
1 - 49
2 - 21
2 - 10
6 - 10
6 - 10
3 - 5
4 - 5
1 - 2
5 - 1


o que eu desejo é o seguinte:
1 - 49
2 - 31
6 - 20
3 - 5
4 - 5
1 - 2
5 - 1


o que estou errando ????


Acmp

Acmp

Curtidas 0

Respostas

Motta

Motta

05/03/2006

select  codiprod,sum(qtda) 
from (
  SELECT CodiProd, SUM(QuanProd) as Qtda from NotaItem  
  where Data between ´01.01.2005´ and ´03.03.2006´ 
  Group by CodiProd 
  Union All 
  SELECT CodiProd, SUM(QuanProd) as Qtda from OrcaItem 
  where Data between ´01.01.2005´ and ´03.03.2006´ 
  Group by CodiProd ) virtual
group by codiprod
order by sum(qtda) desc,codiprod 


vai depender se a sintaxe sql do bd suporta subquery


GOSTEI 0
POSTAR