CODEWITHSUNDEEP
sqlsql-server
eboth225
eboth225

Reputation: 130

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.

Upvotes: 4

Views: 202

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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'.

Upvotes: 2

Related Questions