Double Aggregate

[HSalim]

Hi,

I am trying to get the max value in a range in a way that requires a double
aggregation.
For example, using Northwind,
from the resultset of this query:

select ProductName, OrderID, SUM(Quantity) from OrderDetails
group by ProductName, OrderID
order by ProductName, sum(quantity) Desc

I want to get the OrderID and quantity that ordered the most for a
ProductName
such that I get
Alice Mutton 10607 100
Aniseed Syrup 10540 60
Boston Crab Meat 10895 91
...

Thanks in advance
Habib

[Plamen Ratchev]

Here is one method:

SELECT ProductID, OrderID, qty
FROM (
SELECT ProductID, OrderID, SUM(Quantity) AS qty,
ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SUM(Quantity) DESC) AS rk
FROM [Order Details]
GROUP BY ProductId, OrderID) AS P
WHERE rk = 1
ORDER BY qty DESC;

--
Plamen Ratchev
http://www.SQLStudio.com

[HSalim]

Thank you Plamen!

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:loidnevJHPth5ZjW...@speakeasy.net...

[--CELKO--]

>> I want to get the order_id and quantity that ordered the most for a product_name <<

WITH P(product_name, order_id, qty_tot)
AS
(SELECT product_name, order_id, SUM(order_qty)
FROM Order_Details
GROUP BY product_name, order_id)

SELECT P1.*
FROM P AS P1
WHERE P1.qty_tot
= (SELECT MAX (P2.qty_tot)
FROM P AS P2
WHERE P1.product_name = P2.product_name);

This will handle ties. Get the totals in a CTE, then use it to find
the maximum total quantity.