AnotherGeek AnotherGeek - 3 months ago 8
SQL Question

Performing a sum on rows with condition

I am trying to create a query like this

select sum(t1.quantity) - sum(t2.quantity * SELECT CASE WHEN (condition on t3.status) THEN 1 ELSE 0 END) ),0)
from table1 t1
inner join table2 t2 on t1.key = t2.key
inner join table3 t3 on t2.key = t3.key


Basically what it do is a subtraction between the first some and a second some of t2.quantity that are in rows that have a condition on the column t3.status (for example t3.status > 2).

But I am getting


Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.


Is there a way to do this without adding extra join or other query?

vkp vkp
Answer

I think this is what you are trying to do. You don't need a select again when using case as t3 is already joined.

select sum(t1.quantity) - sum(t2.quantity * CASE WHEN t3.status > 2 THEN 1 ELSE 0 END) 
from table1 t1
inner join table2 t2 on t1.key = t2.key
inner join table3 t3 on t2.key = t3.key