# Multiply two fields if an ID is found in a separate table

Trying to create a Data Extension where we calculate the total spend in a category, if the category exists in a list of promotional categories. The point is that we want to provide an offer when a minimum spend is achieved.

I can't seem to figure this out and have been digging for some time. Below is the part that is tripping me up. CategoryID is in the table being evaludated and there are potential matches in Offers. I tried to `CAST` the results, but not luck. I only want to find the total spend on the product if the product is in the right category.

The ExtPrice field is Decimal(10,2) and the Quantity field is a Number

`CASE when CategoryID in (Select ID from Offers) Then (CAST(ExtPrice as Decimal(10,2)) * CAST(Quantity as Decimal(10,2))) end as Product1_TotalSpend`

If someone can point me in the right direction, it would be most appreciated!

You could do something like this:

``````select
i.orderNum
, sum(
case when o.offerID is not null then i.extprice else 0 end
) promoSpend
, sum (
case when o.offerID is null then i.extprice else 0 end
) nonPromoSpend
from orderItems i
left join Offers o on o.offerid = i.categoryID
group by
i.ordernum
``````

Here's a link to the test: https://rextester.com/VPCU67650

• Thank you. This definitely got me to my answer. What I didn't mention is that I had to multiply the quantity by the price to get to a total spend, but what you provided got me 90% of the way there. I owe you a beverage. – George Rosedale Nov 20 '18 at 6:03