I have two tables BMV_PEDIDO and BMV_PEDIDOITEM and would like the total sum of the value of the orders made on a certain date. Currently I do this by request, that is, I replicate this code 70x.
I would like my query to return all of them.
Following is the current script :
SELECT p.ST_FRETE, SUM(i.NR_QTDE * i.VL_UNITARIO * i.NR_TAXACONVERSAO)
FROM BMV_PEDIDOITEM i
INNER JOIN BMV_PEDIDO p ON p.ID_PEDIDO = i.ID_PEDIDO
WHERE p.ID_PEDIDO IN (45752)
GROUP BY p.ST_FRETE
I believe it to be this:
select
p.ST_FRETE,
SUM(i.NR_QTDE * i.VL_UNITARIO * i.NR_TAXACONVERSAO) as valortotal
from BMV_PEDIDOITEM i
inner join BMV_PEDIDO p on p.ID_PEDIDO = i.ID_PEDIDO
--where p.ID_PEDIDO in( 45752)
where p.datapedido between '2016-01-01' and '2016-03-31'
group by p.ST_FRETE
would like the sum total of the value of the orders made on a certain date
In this case it is not necessary to use the GROUP BY clause. Here's a code hint:
-- código #2
SELECT sum(I.NR_QTDE * I.VL_UNITARIO * I.NR_TAXACONVERSAO) as totalPEDIDO
from BMV_PEDIDOITEM as I
inner join BMV_PEDIDO as P on P.ID_PEDIDO = I.ID_PEDIDO
where P.colunadatapedido = ...
But if the goal is to get the order-to-order sum, it looks like the order code in the result is missing.
-- código #1
SELECT P.ID_PEDIDO,
sum(I.NR_QTDE * I.VL_UNITARIO * I.NR_TAXACONVERSAO) as soma_ITENS_PEDIDO
from BMV_PEDIDOITEM as I
inner join BMV_PEDIDO as P on P.ID_PEDIDO = I.ID_PEDIDO
where P.colunadatapedido = ...
group by P.ID_PEDIDO;