Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    33

    Unanswered: Aggregate where claus problem =/

    Hi Guys,

    Is it possible to have a where clause (or other method) where you only select the max value from this: SUM(ORDER_ITEM.ItemQuantity) and only output that 1 row... or even perhaps a range of rows... in others words... find the ItemID with the greatest combined Quantity

    Heres the query so far:

    SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
    FROM ORDER_ITEM
    GROUP BY ORDER_ITEM.ItemID

    Thx for reading :-)

    --Philkills

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Try this:
    Code:
    SELECT itemid, max(qsum)
    FROM 
    ( 
      SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS qsum
      FROM ORDER_ITEM
      GROUP BY ORDER_ITEM.ItemID
    )
    group by itemid

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

    Try this:
    Code:
    SELECT	TOP 1
    	ORDER_ITEM.ItemID,
    	SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
    FROM	ORDER_ITEM
    GROUP BY ORDER_ITEM.ItemID
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by blindman
    Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

    Try this:
    Code:
    SELECT	TOP 1
    	ORDER_ITEM.ItemID,
    	SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
    FROM	ORDER_ITEM
    GROUP BY ORDER_ITEM.ItemID
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

    ah thx man perfect ^^

    Although, is it not inefficient to have 2 sums for the same thing? or will SQL realise that their the same thing and count them as such..

    Also would be it possible to change the query to select all Total's that are greater than say... 10..

    like:

    Code:
    SELECT
    	ORDER_ITEM.ItemID,
    	SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
    FROM	ORDER_ITEM
    WHERE SUM(ORDER_ITEM.ItemQuantity) > 10
    GROUP BY ORDER_ITEM.ItemID
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    although this doesn't work as it doesn't like that in the where clause =/
    Last edited by Philkills; 12-05-06 at 17:06.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

    To filter on an aggregate value you must use the HAVING clause:
    Code:
    SELECT
    	ORDER_ITEM.ItemID,
    	SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
    FROM	ORDER_ITEM
    GROUP BY ORDER_ITEM.ItemID
    HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by blindman
    Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

    To filter on an aggregate value you must use the HAVING clause:
    Code:
    SELECT
    	ORDER_ITEM.ItemID,
    	SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
    FROM	ORDER_ITEM
    GROUP BY ORDER_ITEM.ItemID
    HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    thx mate, lol seems kinda pointless "having" an extra keyword just to perform those extra operations ;p (that is instead of where)

Posting Permissions

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