mysql - using SUM()

Asked

Viewed 2,850 times

1

I need to sum the total value of an order with more than 1 product included with different unit values.

follow tb_request:

id_pedido    | id_produto    | valor_unit | qtd  
-------1-----|-------3-------|----10.00---|--2--  
-------1-----|-------5-------|-----9.00---|--6--

my select: 

   select pedido.id_produto, sum(pedido.qtd),
   ((select sum(valor_unit) from tb_pedido where id_produto = 
   pedido.id_produto) * pedido.qtd) 
   from tb_pedido pedido 
   where id_pedido = 1

the result should be 74.00, but all the select that I do the result is (10.00 * 8 = 80.00) or (9.00 * 8 = 72.00).

the select handle 1 of the two valor_unit and multiplies by the sum of Qtd

mysql

  • Do you want the total order value? Your select should not work. There is a syntax error. What exactly do you want to do?

  • jura, qual erro ? yes, I want full order value.

  • 1

    mysql (bizarrely) does not trigger an error, but a query with fields and aggregators (sum, max, Count, etc.) requires a group by. In Mysql it returns only to the first line. But conceptually it is wrong. See in Sqlfiddle. I took out the Where but only the first line was retouched (order 1, product 3)

1 answer

2


You can group by request and add the multiplication thus:

select id_pedido,
       sum(valor_unit*qtd) as total
  from tb_pedido
 group by id_pedido

See example working: http://sqlfiddle.com

  • It worked perfect ! Too simple, but I wasn’t squeezing. Thanks.

Browser other questions tagged mysql

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