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 :-)

--PhilkillsTry this:
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|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
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|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
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:

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 =/|||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:
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|||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:
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)