eboth225 eboth225 - 1 month ago 10
SQL Question

Subtract aggregate of only certain rows from only other certain rows in the same table?

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


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

All sales regardless of type accumulate into regular, but sale and feature also accumulate into their own "saletype" also. So in the above example, I've sold 10 products total (7 regular, 1 sale, 2 feature).

I want to return the regular quantity minus the other two columns as efficiently as possible, and also the other saletypes too. Here is how I am currently doing it:

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 yields what I am after:

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


But I feel like there has to be a better way than essentially querying the same information twice.

vkp vkp
Answer Source

You can use the window function sum and some arithmetic to do this.

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

This assumes there is atmost one row for saletype 'regular'.