So, here are my tables:

Sales

Products

I need to calculate how much benefit did I get from the products which have "Red Bull" in its name. Benefit is equal to price-real_price.

Expected output:

Here is what I tried:

SELECT products.product_code,(price-real_price) as profit
FROM products
INNER JOIN sales
ON products.product_code = sales.product_code
outer apply (select count(*) 
            from sales as benefit
where product_name like '%red bull';

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

CodePudding user response：

create table Sales (
   id int, 
   product_code int)

create table Products(
   product_code int,
   product_name char(100),
   price smallmoney,
   real_price smallmoney)

insert into Sales values   
   (1, 4536),
   (2, 4674),
   (3, 4536) -- to have multiple sales for Red Bull
   
insert into Products values
   (4536, 'Red bull energy drink 300 ml', 3.68, 2.88),
   (4674, 'Mac coffee 25 gr', 2.59, 2.10)
   
select 
   p.product_name, sum(p.price - p.real_price) benefit
from Sales s join Products p on s.product_code = p.product_code
group by p.product_name

CodePudding user response：

As you say that each line is one quantity sold the following query will do the job.
I suggest that it would be better to have a quantity column in the sales table.

select
  p.product_code,
  p.product_name,
  count(s.product_code) as quantity_sold,
  sum( (p.price-p.real_price)*count(s.product_code) as total_profit
from products p 
join sales s on s.product_code = p.product_code
where p.product_name like '%red bull'
group by
  p.product_code,
  p.product_name;