Calculate Value of SQL columns

Asked

Viewed 950 times

0

I have two tables BMV_PEDIDO and BMV_PEDIDOITEM and would like the total sum of the value of the orders made on a given date. I currently make this request per request, ie, I replicate this code 70x.

I would like my query to return all.

Follows the script current:

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
sql sql-server

  • p.ID_PEDIDO in( 45752) ? is when you have 1000 requests will repeat 1000 times the same code?

  • @Juniortorres: In addition to the "total sum of the order value made on a given date", do you also need the date requests to be listed? Or only the total value, without lines of detail?

2 answers

3

I believe that’s it:

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

  • No friend, it returns me the sum of all. I would like the sum of each request;

  • 2

    You have your order and your order items and when you make the "Inner Join" you are bringing the items of a specific order, and adding up the totals, if you do not inform the order id it will calculate of all orders.

  • As you describe above, you want the total sum of the order value made on a given date. In this case this query that @itasouza wrote would solve, Now if you want by request there need to add the order number in the clause WHERE.

0

would like the total sum of the order value made on a given date

In this case it is not necessary to use the clause GROUP BY. Here is the code suggestion:

-- 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 requested sum on request, it seems to me that the order code is missing in the result.

-- 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;

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.