Mais Vendidos entre Pedidos e Vendas
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 ????
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
Curtidas 0
Respostas
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