SQL Calculations

How can I calculate pointdeductions based on the productid in the orderhistory?
When I try to add the case statement, i get double entries, even when I add productid
to the group by clause.



SELECT            SUM([Order Details].UnitPrice * [Order Details].Quantity) AS OrderTotal,
                  Orders.CustomerID,
                  Floor(SUM([Order Details].UnitPrice * [Order Details].Quantity) / 400) * 10 As EarnedPoints                  
                  
FROM         Orders INNER JOIN
             [Order Details] ON Orders.OrderID = [Order Details].OrderID

WHERE  Orders.OrderDate BETWEEN '07/01/1996' AND '07/31/1996'

GROUP BY Orders.CustomerID


If the user has one of these products subtract 10, else 0
CASE WHEN [Order Details].ProductID IN (11, 42, 77, 51, 72, 71)
THEN '-10'
ELSE '0'
END
AS PointDeductions

This is from NorthWinds Sample DB
LVL 8
JRockFLAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
try like this

SELECT            SUM([Order Details].UnitPrice * [Order Details].Quantity) AS OrderTotal,
                  Orders.CustomerID,
                  Floor(SUM([Order Details].UnitPrice * [Order Details].Quantity) / 400) * 10 As EarnedPoints     ,
sum(CASE WHEN [Order Details].ProductID IN (11, 42, 77, 51, 72, 71)
THEN '-10'
ELSE '0'
END)
AS PointDeductions            
                 
FROM         Orders INNER JOIN
             [Order Details] ON Orders.OrderID = [Order Details].OrderID

WHERE  Orders.OrderDate BETWEEN '07/01/1996' AND '07/31/1996'

GROUP BY Orders.CustomerID

0
 
JRockFLAuthor Commented:
Thank you, that is almost what I had except i was missing the SUM()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.