Answered by:
TOP 5 Command with mathematical equation
Question

Ok here is what I am trying to do. I have a "Order Details" table, I know the space is stupid, I didnt put it there. The columns are: OrderID PK, ProductID PK, UnitPrice, Quantity, and Discount.
I want to return the TOP 5 order totals, but I dont want to make a new column for the totals. Can anyone help?
Answers

Something like this?
SELECT TOP 5OrderID, SUM(UnitPrice*Quantity)
FROM Order Details
GROUP BY OrderID
ORDER BY SUM(UnitPrice*Quantity) DESC
Or is the logic slightly more complicated? You may be able to use CTEs or derived tables to achieve that.
HTH!
every day is a school day Marked as answer by Kenneth Coggin Saturday, April 4, 2009 3:51 PM
All replies

Something like this?
SELECT TOP 5OrderID, SUM(UnitPrice*Quantity)
FROM Order Details
GROUP BY OrderID
ORDER BY SUM(UnitPrice*Quantity) DESC
Or is the logic slightly more complicated? You may be able to use CTEs or derived tables to achieve that.
HTH!
every day is a school day Marked as answer by Kenneth Coggin Saturday, April 4, 2009 3:51 PM

Hi,
Try this
SELECT TOP 5
OrderID
,ProductID
,(SUM(UnitPrice*Quantity)  SUM(Discount)) AS Total
FROM OrderDetails
GROUP BY OrderID, ProductID
ORDER BY (SUM(UnitPrice*Quantity)  SUM(Discount)) DESC
I hope it might be helpful for you
Rajesh Jonnalagadda http://www.ggktech.com 
Something like this?
SELECT TOP 5OrderID, SUM(UnitPrice*Quantity)
FROM Order Details
GROUP BY OrderID
ORDER BY SUM(UnitPrice*Quantity) DESC
Or is the logic slightly more complicated? You may be able to use CTEs or derived tables to achieve that.
HTH!
every day is a school day
You rock!! Thank you very much!