Solved

# SQL Calculations

Posted on 2007-08-10
Medium Priority
166 Views
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
0
Question by:JRockFL

LVL 39

Accepted Solution

appari earned 2000 total points
ID: 19671483
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

LVL 8

Author Comment

ID: 19671701
Thank you, that is almost what I had except i was missing the SUM()
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month16 days, 12 hours left to enroll