none
TOP 5 Command with mathematical equation RRS feed

  • 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

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
    Friday, April 3, 2009 4:02 PM
    Moderator

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
    Moderator
  • 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
    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