Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Unanswered: Displaying additional columns when using MAX and subquery

    I am taking an SQL class in college and have been trying to get this to produce the desired result for quite a while with no luck.

    I am using the Northwind database included in MS SQL Server 2008.

    This is the code I have so far (that works)....

    SELECT MAX(Total) AS BiggestOrder FROM (SELECT SUM(UnitPrice * Quantity * (1 - Discount)) AS Total FROM [Order Details] GROUP BY OrderID) AS A

    It displays the largest order total as I expect.


    BiggestOrder
    ------------
    16387.5


    The problem is that this information is useless unless we know which OrderID it is associated with. So, my question is....how do I get this to display the OrderID in the column left of "BiggestOrder"??

    I have tried adding OrderID to the initial SELECT statement, but that results in an error "Invalid column name 'OrderID'."


    I think I have stumped my professor, as she seems confused by this query. Any one have any ideas...???
    Last edited by myqalan1975; 09-15-08 at 16:31.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by myqalan1975
    I think I have stumped my professor, as she seems confused by this query.
    you need a new professor!!

    i was just on my way out the door, so i will check back later and see if you have received a reply from one of the other top SQL experts we have on this forum

    no, srsly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2008
    Posts
    5

    Displaying additional columns when using MAX and subquery

    I am taking an SQL class in college and have been trying to get this to produce the desired result for quite a while with no luck.

    I am using the Northwind database included in MS SQL Server 2008.

    This is the code I have so far (that works)....

    SELECT MAX(Total) AS BiggestOrder FROM (SELECT SUM(UnitPrice * Quantity * (1 - Discount)) AS Total FROM [Order Details] GROUP BY OrderID) AS A

    It displays the largest order total as I expect.


    BiggestOrder
    ------------
    16387.5


    The problem is that this information is useless unless we know which OrderID it is associated with. So, my question is....how do I get this to display the OrderID in the column left of "BiggestOrder"??

    I have tried adding OrderID to the initial SELECT statement, but that results in an error "Invalid column name 'OrderID'."


    I think I have stumped my professor, as she seems confused by this query. Any one have any ideas...???

  4. #4
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by r937

    i was just on my way out the door, so i will check back later and see if you have received a reply from one of the other top SQL experts we have on this forum

    Sorry....I realized this was posted in the wrong forum and have reposted it there....

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I do not have a Northwind laying around but try this...

    edited: I am a big dummie

    Have a good semester. Any hot girls in class?
    Last edited by Thrasymachus; 09-15-08 at 22:25.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by Thrasymachus
    I do not have a Northwind laying around but try this...

    SELECT OrderID,MAX(SUM(UnitPrice * Quantity * (1 - Discount)))
    FROM [Order Details]
    GROUP BY OrderID
    That results in the following...

    Msg 130, Level 15, State 1, Line 2
    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.



    Quote Originally Posted by Thrasymachus
    Have a good semester. Any hot girls in class?
    The classes are online...so I have no idea!! LOL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sorry. end of the day moment. you can do it this way...

    SELECT TOP 1 X.OrderID,X.the_sum
    FROM
    (SELECT ORDERID,SUM(UnitPrice * Quantity * (1 - Discount)) as the_sum
    FROM [Order Details]
    GROUP BY OrderID) X
    ORDER BY X.the_sum DESC

    tired. long long day
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT OrderID
         , SUM(UnitPrice * Quantity * (1 - Discount)) AS Total 
      FROM [Order Details] 
    GROUP 
        BY OrderID
    ORDER
        BY Total DESC LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, i just realized that you don't actually want a MySQL solution

    so the LIMIT 1 won't work

    go with the TOP 1 answer in post #7

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by Thrasymachus
    SELECT TOP 1 X.OrderID,X.the_sum
    FROM
    (SELECT ORDERID,SUM(UnitPrice * Quantity * (1 - Discount)) as the_sum
    FROM [Order Details]
    GROUP BY OrderID) X
    ORDER BY X.the_sum DESC

    Thanks. That works great. I haven't learned the TOP command yet. I knew there had to be an easy way to accomplish this.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •