Sum with case condition

AbeSpain
AbeSpain used Ask the Experts™
on
	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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
Commented:
Put hte case inside your sum.

Try this:
select 
ProductVariationNoDiscount,
sum (case when ProductVariationNoDiscount = 1  then
             (OP.ProductVariationPrice * (1 - Coalesce(PromotionsPercentage, 0)))
         else
		When 2 Then 	
			((ProductVariationPrice))
		End) as Total,
count(ProductVariationiD) as counter
from
	FROM         
		tblOrder AS O INNER JOIN
		tblOrderProduct AS OP ON O.OrderiD = OP.OrderiD

group by
ProductVariationNoDiscount

Open in new window

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

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial