# 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
###### Who is Participating?

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

Author 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.