SQL Sum with conditions in a select case

Question

have the following sql query

select catalogid
   , sum(numitems) numitems
   , sum(allitems) - sum(numitems) ignoreditems
from
(
   select i.catalogid
      , case
           when (ocardtype in ('PayPal','Sofort') OR
                   ocardtype in ('mastercard','visa') and
                   odate is not null)
              AND NOT EXISTS
              (
                 select *
                 FROM bookedordersids b
                 where b.booked = o.orderid
              )
           then numitems
           else 0
        end AS numitems
      , numitems AS allitems
   from orders AS o
   join oitems AS i on i.orderid = o.orderid
) AS X
group by catalogid

now i've 2 tables Here orders and oitems table

the query sums the numitems and ignoreditems based on the conditions you see, now what if i want to find the sum only when the value of a column called oprocessed in oitems table is 0 ,

do i add the following before X

where oprocessed=0

or should i add a condition to the SELECT CASE?

"now what if i want to find the sum only ...". To check, which sum are you referring to - the sum making up ignored items, the one for numitems (which would affect ignored), or some other figure / result? — JohnLBevan, Oct 20 '12 at 01:31
no i dont want allitems to be affected, i guess the WHERE oprocessed=0 is the right thing to use, right? — user1570048, Oct 20 '12 at 01:38
If you put in a where clause then allitems would be updated - since the where applies to all catalog ids, numitems (returned as allitems) and the case statement. If you add the condition in the case statement only the sum of numitems (returned as numitems) will be affected. — JohnLBevan, Oct 20 '12 at 01:59

JohnLBevan · Accepted Answer · 2012-10-20T01:34:14.897

Your catalog id is coming from the oitems table - adding where oprocessed=0 would mean that those catalog numbers aren't included in your results.

My guess is you'd therefore want this in your case statement - but I'm not entirely sure on the spec behind this so can't say for sure.

select catalogid
, sum(numitems) numitems
, sum(allitems) - sum(numitems) ignoreditems
from 
(
    select i.catalogid
    , numitems allitems
    , case 
        when --if the money for the order is gaurenteed return the number of items bought
        (
            ocardtype in ('PayPal','Sofort') 
            OR
            (
                ocardtype in ('mastercard','visa') 
                and
                odate is not null
            )
        ) 
        AND NOT EXISTS 
        (
            select top 1 1
            FROM bookedordersids b
            where b.booked = o.orderid
        )
        and i.oprocessed = 0
        then numitems
        else 0 --if payment isn't made/gaurenteed 0 items bought
    end numitems
    from orders o
    inner join oitems i 
    on i.orderid = o.orderid
) X
group by catalogid

SQL Sum with conditions in a select case

1 Answers1