How to get in which store did the specific product get the most benefit?

So, here are my tables:

Sales

id	product_code	store_id
1	4536	1
2	4674	2

Products

product_code	product_name	price	real_price
4536	Red bull energy drink 300 ml	3,68	2,88
4674	Mac coffee 25 gr	2,59	2,10

Stores

store_id	store_name
1	first
2	second

I need to find in which store the product ‘red bull’ got the most benefit. Benefit is considered as price-real_price. Quantity is considered as repetition of product_code in sales table.

Expected output:

product_name	benefit	store_name
Red bull energy drink 300 ml	4536,4	second

Here is what I tried:

select 
   products.product_name, (sum( price-real_price)*count(sales.product_code)) as benefit from products
join sales on sales.store_id = stores.store_id
where products.product_name like '%red bull%'
group by products.product_name

But it does not give me the output I want to get.

Answer

You can find the store in question by grouping by store_id and ordering by the benefit.

select top(1)
   sales.store_id, sum(price-real_price)*count(sales.product_code) as benefit 
from products
join sales on sales.product_code = products.product_code
where products.product_name like '%red bull%'
group by sales.store_id
order by sum(price-real_price)*count(sales.product_code) desc