I need to present the product with the highest gross sales

Question

SELECT p.descricao_prod
FROM produto p
INNER JOIN venda v ON v.cod_prod = p.codigo
WHERE p.preco = (SELECT MAX(p.preco) FROM produto p)

I tried with this code, but, it only presents me the product with higher price. However, I need the product with higher sales...

Here are photos of the necessary tables:

Somehow I need to add the sales of each product to see which is the highest value, but, I can’t imagine how to do that... Can someone help me??!!

Answer 1

In my view there is an error in your modeling, consider reviewing, because you do not record the value of the product in the sales table, imagine the following scenario:

1. In your product list the value of the pen is $ 2,00, you make a sale today from the last pen in stock, and the next day the buyer of your company buys a new batch of the same pen, but for a higher price, then your sale price ceases to be $ 2,00 and becomes $ 2,50, If you use as reference the value registered in the product table to determine the value of your billing, all pens that have already been sold will assume the new value that is $ 2,50;

2. The sales table represents a fact, and you should store the value of the product in it, it will help you answer questions like:

2.1 How much did the pen cost in 2020? 2.1 Average pen value over the last 6 months;

Also only then will you get the real value of your billing.

Anyway I understood your question, follow the query I believe is what you need, when you change your modeling, just change b. price for a. value where value will correspond to the value of the product at the time of sale.

Below Query idea based on your problem:

1. Perform the Join between the sales table and product;
2. Add the total value of "sale" by product;
3. Sort decreasingly (Larger to smaller);
4. Limit the result;

This template is cool because you can make your query more versatile, and just by manipulating the limit, you could show the 5 products with higher billing value for example.

I hope I’ve helped

 
select b.codigo, b.descricao_prod, sum(b.preco) as vltotal
 from venda a inner join produto b on (a.cod_prod = b.codigo)
 group by b.codigo, b.descricao_prod
 order by vltotal desc
 limit 1;