?
Solved

SQL Calculations

Posted on 2007-08-10
2
Medium Priority
?
166 Views
Last Modified: 2012-05-05
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
Comment
Question by:JRockFL
2 Comments
 
LVL 39

Accepted Solution

by:
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

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question