Query issue in SQL Server 2008

Question

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

The total amount of units sold of each product
The name of the product
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!

You might want to add a TOP 1 in case you have multiple customers who have both bought the same MAX count — Adam V
– Adam V, Commented Mar 30, 2012 at 14:58
Could you post some sample data? what you feed it, what you want to get out, what you are getting out? — wax eagle
– wax eagle, Commented Mar 30, 2012 at 14:59

GarethD · Accepted Answer · 2012-03-30 15:12:03Z

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

Nick O · Accepted Answer · 2012-03-30 15:16:20Z

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

answered Mar 30, 2012 at 15:16

Nick O

3,8567 gold badges42 silver badges50 bronze badges

2 Comments

Jean Carlos Suárez Marranzini Over a year ago

Can you build a solution without the CROSS ?

Jean Carlos Suárez Marranzini Over a year ago

It works perfectly but it would be possible to build a solution without the CROSS APPLY ? Using only the clauses I've used in my code ? Maybe using HAVING ?

Collectives™ on Stack Overflow

Query issue in SQL Server 2008

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related