So, here are my tables:

Sales

Products

Stores

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:

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.

CodePudding user response：

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

CodePudding user response：

Maybe a query like below will help. Note that I have used dense rank function to get all stores which have same benefit in case of ties. Also it neatly allows the logic to be changed later.

demo link

; with benefitperstore as 
(
select 
s.store_name,
P.product_name, 
count(1) * (P.price - P.real_price) as benefit
from Sales X join Stores s 
   on X.Store_id=s.Store_id
join Products P 
   on P.product_code=X.product_code
where product_name like '%red bull%'
group by s.store_name,P.price- P.real_price,P.product_name
)
, mostBenefit as
(
select *, 
dense_rank() over (order by benefit desc) as rn 
from benefitperstore 
)

select 
product_name,
benefit,
store_name
from mostBenefit 
where rn=1