Total return of each order with MySQL?

Question

Total return of each order with MySQL?

Navigation

#1 by (3 votes)

2

Imagine that I have a pedidos table with the following fields:

And that I have two other tables ( pedido_a , pedido_b ) of two distinct types of requests like the examples below:

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

What I'm trying to do is create a query that returns a list with all the requests ( pedidos ) along with the total value of each, according to their respective type.

The logic is already defined, but I'm having trouble trying to pass it to SQL. Here is an image illustrating the process I envisioned:

How could I pass this logic to a SQL query?

mysql sql

asked by anonymous 04.08.2014 / 00:43

1 answer

Converting String to Date and adding days to a specific date. Traversing Array within a String

score 3 · Accepted Answer

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

Follow the example in SQL Fiddle