Mysql query with mixed ORDER BY?

Asked

Viewed 80 times

0

My doubt is a little 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 (many mapping to many), venda and itens_venda.

I need a consultation for a report that returns the amount of outputs (in sales) of these products, and the stipulated income that these outputs generated. I also want to maintain the relationship of preco_produto, because it is to be clear that the outputs of that product were made at the time that a certain price was worth.

So I came in for a consultation sort of 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

Only this returns the records to me 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 in fact I wanted them returned like this, ordered 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 SQL query?

PS.: If you need any more details let me know.

mysql sql

  • Let me see if I understand "Esfirra" has the biggest "Qtd", so first order by itself that there is a "20" for "Esfira" and a "35" for "Coxinha"? This being create a column max(iv.quantity) AS maxqtd grouping by product and sort by this 'max"

  • That’s right @Motta. But if I group by product I lose the "preco_product" ratio and then instead of appearing two esfirras in the result, only one will appear. As for the MAX(iv.quantity), it will not work because if I sort by the largest amount of selling items, it will not solve. Maybe if I did something like MAX(SUM(iv.quantidade)), but Mysql wouldn’t let me do it.

  • How it comes out by changing the order: ORDER BY p.ASC name, Qtd DESC?

  • @Reginaldorigo, the result comes out in ascending order by product name. For example, first would come the "Beirut" with 30, then the "Coxinha" with 35 and then the "Esfirra" with 40, with the "Esfirra" with 20 soon after.

  • You can put a query out of this by taking the nicknames and using them in ORDER BY.

  • If you order first by product and then by quantity does not work?

Show 1 more comment

1 answer

1

Try this 

select *
from
(
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 p.nome , pp.preco_venda
) c1,
SELECT p.nome AS produto, max(iv.quantidade) AS maxqtd 
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 p.nome , pp.preco_venda
) c2
where c1.produto = c2.produto
--order by c2.maxqtd , c1.produto , c1.qtd
order by c1.produto , c2.maxqtd , c1.qtd--alterado depois

  • Ah, I thought I could have solved it, but I need the group by by the product id, otherwise it doesn’t keep track of the price. Without group by, records are sorted correctly, but I lose the price ratio. Even trying where c1.pp_id = c2.pp_id didn’t work out.

  • Maybe it has to be order by C1.product , C2.maxqtd , C1.Qtd

  • Unfortunately not yet solved. In a way this brings me a result similar to the first query I posted here. :/

  • forgets , the solution vei because it needed the "greatest Qtd" ... my stupidity , I think I did not understand the issue of "lose the price ratio"

  • I will try to explain. I have products registered in the table produto, right? Every time I want to change the price of a particular product, instead of me registering a new product, I register a new price in the table preco_produto. So actually the listing I want in the querie is not of the products but of the "price-product". I want to be clear in the listing that came out that QTD sold and that INCOME while that PRICE was valid for a certain product. What may have happened is that maybe I’m not being able to explain it properly.

  • Anyway, I really appreciate the try. I think the path can be right there. I’m just not getting a glimpse yet. Should you have any change, I come and accept your reply (:

Show 1 more comment

Browser other questions tagged mysql sql

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