asked on

Sum with case condition

	SELECT     
		Sum((OP.ProductVariationPrice * (1 - Coalesce(PromotionsPercentage, 0)))), 
		Sum((OP.ProductVariationTotalCost * (1 - Coalesce(PromotionsPercentage, 0)))),
		Count(ProductVariationiD)
	FROM         
		tblOrder AS O INNER JOIN
		tblOrderProduct AS OP ON O.OrderiD = OP.OrderiD

Open in new window

How do I make this so I can put a case condition on productvariationprice (sometimes it has a discount applicable, sometimes it doesn't)? i.e.

		Case ProductVariationNoDiscount
		When 1 Then
			(OP.ProductVariationPrice * (1 - Coalesce(PromotionsPercentage, 0)))
		When 2 Then 	
			((ProductVariationPrice))
		End,

Open in new window

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

k_murli_krishna

Do it this way:

SELECT
Sum((OP.ProductVariationPrice * (1 - CASE When PromotionsPercentage IS NULL
Then 0 Else PromotionsPercentage))),
Sum((OP.ProductVariationTotalCost * (1 - CASE When PromotionsPercentage IS NULL
Then 0 Else PromotionsPercentage))),
Count(ProductVariationiD)
FROM
tblOrder AS O INNER JOIN
tblOrderProduct AS OP ON O.OrderiD = OP.OrderiD

AbeSpain

ASKER

That second one wont work; the discount can be turned off at the product level (ProductVariationNoDiscount). So, someone can have a discount code applied (PromotionsPercentage) but only some of the products on the order are eligible.