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?
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