Search code examples
sqlselectsubquery

SQL selecting with conditioning from a subquery

I am trying to perform two sum functions from a query. However, I want to only perform one of the sum functions if it meets a certain condition without affecting the other sum function.

What I was thinking is to use something similar to select x where condition = 1 from AC which is however not possible.

Here is the sample query where I want the second [sum(t.match)] selection to only calculate if the result in the subquery: match = 1 while still getting the total sum of all qqty.

select 
    sum(t.qqty), sum(t.qqty)  
from
    (select 
         car, cqty, qqty, 
         case when cqty = qqty then 1 else 0 end as match, 
         location, state) t
Solution

  • Use conditional aggregation -- that is case as the argument to the sum():

    select sum(t.qqty), sum(case when condition = 1 then t.qqty else 0 end)
from t;