My question is a bit boring to explain but I'll try to make it as simple as possible.
I have the following tables in a MySQL database: produto
, preco
, preco_produto
(mapping many to many), venda
and itens_venda
.
I need a query for a report that returns me the amount of outflows (in sales) of those products, and the stipulated income that these outputs generated. I also want to keep the preco_produto
relationship, because it is to be clear that the outputs of that product were made at the moment a certain price was worth.
So I came up with a query something like this:
SELECT p.nome AS produto, SUM(iv.quantidade) AS qtd, pp.preco_venda AS preco,
(SUM(iv.quantidade)*pp.preco_venda) AS renda
FROM tb_venda v
INNER JOIN tb_item_venda iv
ON (v.id = iv.tb_venda_id)
INNER JOIN tb_preco_produto pp
ON (iv.tb_preco_produto_id = pp.id)
INNER JOIN tb_produto p
ON (pp.tb_produto_id = p.id)
WHERE v.tb_status_venda_id = 3
GROUP BY pp.id
ORDER qtd DESC, BY p.nome ASC
But this returns the records as follows:
+---------+-------+-------+-------+
| produto | qtd | preco | renda |
+---------+-------+-------+-------+
| Esfirra | 40 | 2.2 | 88 |
| Coxinha | 35 | 2 | 70 |
| Beirute | 30 | 2.5 | 75 |
| Esfirra | 20 | 2 | 40 |
When I actually wanted them returned like this, sorted by quantity and by name:
+---------+-------+-------+-------+
| produto | qtd | preco | renda |
+---------+-------+-------+-------+
| Esfirra | 40 | 2.2 | 88 |
| Esfirra | 20 | 2 | 40 |
| Coxinha | 35 | 2 | 70 |
| Beirute | 30 | 2.5 | 75 |
Is there any way to get this result working only in the SQL query?
PS: If you need more detail let me know.