Query issue in SQL Server 2008

Question

The query I'm trying to develop return the following:

1. The total amount of units sold of each product
2. The name of the product
3. The CustomerID that has bought the max amount of each product

This is what I have so far:

SELECT DISTINCT 
   Products.ProductName, 
   SUM([Order Details].Quantity) as cant, 
   Orders.CustomerID       
FROM 
  Products 
INNER JOIN [Order Details] 
    ON Products.ProductID = [Order Details].ProductID 
INNER JOIN Orders 
    ON [Order Details].OrderID = Orders.OrderID
WHERE 
  [Order Details].Quantity = 
  (
      SELECT 
        MAX([Order Details].Quantity) 
      FROM 
        [Order Details]
      WHERE 
        [Order Details].ProductID = Products.ProductID
  )
GROUP BY 
  Products.ProductName, Orders.CustomerID

It's not giving me the results expected.

Any information related to the contents of the tables or anything else, just post it in a comment and I'll answer.

Thanks in advance for the help!

Answer 1

Try this

SELECT DISTINCT 
   p.ProductName, 
   SUM(od.Quantity) as cant, 
   o.CustomerID       
FROM 
  Products p
  INNER JOIN [Order Details] od
    ON p.ProductID = od.ProductID 
  CROSS APPLY (
      SELECT 
        MAX(Quantity) Quantity
      FROM 
        [Order Details]
      WHERE 
        ProductID = p.ProductID
      ) mq
  INNER JOIN [Order Details] fod
    ON od.ProductID = fod.ProductID
    AND mq.Quantity = fod.Quantity
  INNER JOIN Orders o
    ON fod.OrderID = o.OrderID
GROUP BY 
  p.ProductName, o.CustomerID

Answer 2

Try this,

;WITH orderCTE AS 
(   SELECT  p.ProductName,
            o.CustomerID,
            SUM(od.Quantity) [Quantity]
    FROM    Products p
            INNER JOIN [Order Details] od
                ON od.ProductID = p.ProductID
            INNER JOIN Orders o
                ON o.OrderID = od.OrderID
    GROUP BY p.ProductName, o.CustomerID
)
SELECT  ProductName, [TotalQuantity], CustomerID
FROM    (   SELECT  CustomerID, 
                    ProductName,
                    Quantity,
                    MAX(Quantity) OVER(PARTITION BY ProductName) [MaxQuantity],
                    SUM(Quantity) OVER(PARTITION BY ProductName) [TotalQuantity]
            FROM    orderCTE
        ) ord
WHERE   MaxQuantity = Quantity  

EDIT

The above will return duplicates if more than one customer has order the same product the maximum amount of times. This can be avoided by using the below, which will return a semi-colon separated list of customer IDs that have order each product the max number of times:

;WITH orderCTE AS 
(   SELECT  p.ProductName,
            o.CustomerID,
            SUM(od.Quantity) [Quantity]
    FROM    Products p
            INNER JOIN [Order Details] od
                ON od.ProductID = p.ProductID
            INNER JOIN Orders o
                ON o.OrderID = od.OrderID
    GROUP BY p.ProductName, o.CustomerID
), MaxOrdersCTE AS
(   SELECT  CustomerID, 
            ProductName,
            Quantity,
            MAX(Quantity) OVER(PARTITION BY ProductName) [MaxQuantity],
            SUM(Quantity) OVER(PARTITION BY ProductName) [TotalQuantity]
    FROM    orderCTE
)

SELECT  ProductName, 
        [TotalQuantity], 
        STUFF(( SELECT  ';' + CONVERT(VARCHAR, CustomerID)
            FROM    MaxOrdersCTE c
            WHERE   ord.ProductName = c.Productname
            AND     MaxQuantity = Quantity
            FOR XML PATH('')
        ), 1, 1, '') [CustomerIDs]
FROM    MaxOrdersCTE ord
WHERE   MaxQuantity = Quantity