# 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?
Friday, April 3, 2009 3:40 PM

• 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
Friday, April 3, 2009 4:02 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
Friday, April 3, 2009 4:02 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
Saturday, April 4, 2009 6:37 AM
• 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!
Saturday, April 4, 2009 3:51 PM