Subtract a collection of only certain rows from other specific rows in a single table?

Question

product     saletype    qty
-----------------------------
product1    regular     10
product1    sale        1
product1    feature     2

I have a sales table, as shown above, and products can be sold 1 of 3 different ways (regular price, sale price or function price).

All sales, regardless of type, accumulate in regular sales, but sales and function also accumulate in their own "saletype". So, in the above example, I sold 10 products (7 ordinary, 1 sale, 2 functions).

I want to return the correct amount minus the other two columns as well as other salt modes as efficiently as possible. Here's how I do it now:

create table query_test
(product varchar(20), saletype varchar(20), qty int);

insert into query_test values 
('product1','regular',10),
('product1','sale',1),
('product1','feature',2)

select
    qt.product,
    qt.saletype,
    CASE WHEN qt.saletype = 'regular' THEN sum(qt.qty)-sum(lj.qty) ELSE sum(qt.qty) END as [qty]
from
    query_test qt
    left join
    (
        select product, sum(qty) as [qty]
        from query_test
        where saletype in ('sale','feature')
        group by product
    ) lj on lj.product=qt.product
group by
    qt.product, qt.saletype;

... which gives what I get after:

product     saletype    qty
-----------------------------
product1    feature     2
product1    regular     7
product1    sale        1

But I feel that there should be a better way than basically requesting the same information twice.

Answer 1

sum .

select product,
saletype,
case when saletype='regular' then 2*qty-sum(qty) over(partition by product) 
else qty end as qty
from query_test

, saletype "" .