Return total of each order with Mysql?

Asked

Viewed 950 times

2

Imagine I have a table pedidos with the following fields:

And that I have two other tables (pedido_a, pedido_b) of two different types of applications as the examples below:

Each item in the table pedidos may have "n" table items pedido_a OR of pedido_b. An order may be of only one type ("A" or "B", never a mixture of the two).

What I’m trying to do, is build a query that returns a list of all requests (pedidos) together with the total value of each, according to its type.

The logic is already defined, but I’m having problems trying to pass it to SQL. Here is an image illustrating the process I imagined:

How could I pass this logic to an SQL query?

mysql sql

  • if you create a total order field in the main order table, and at the time of order creation, calculate the total and record there, I think it would make your life much easier...

  • @Jader has already thought of this alternative and it is really a good solution. But the problem is that currently this is not a possibility (changing the structure of the table and/or the code). Consultations may only be held.

  • The valor in the tables is already the total value of the application or it is necessary to multiply by the quantities?

  • @Anthonyaccioly The Column valor refers to the unit value, therefore it is necessary to multiply by the quantities.

1 answer

3


I made the query assuming that it is necessary to multiply the quantity by the value, but if it is not just remove the multiplication.

select p.id
 , p.tipoPedido
 , IF( p.tipoPedido = 'A'
     , SUM(pa.quantidade * pa.valor)
     , SUM((pb.quantidadeTamanho1 + pb.quantidadeTamanho2 + pb.quantidadeTamanho3) * pb.valor))
  from pedido p
  left join pedido_a pa on p.id=pa.idPedido
  left join pedido_b pb on p.id=pb.idPedido
 group by p.id

follows the example in SQL Fiddle

  • Yes, the amount needed to be multiplied by the value, and the query worked perfectly. Thank you!

Browser other questions tagged mysql sql

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