I have the following tables:

Product_T with columns:
ProductID,
ProductDescription

OrderLine_T with columns:
OrderID,
ProductID,
OrderedQuantity

Order_T with columns:
OrderID,
CustomerID,

Customer_T with columns:
CustomerID,
CustomerName

I want to list the product ID and description, along with the customer ID and name for the customer who has bought the most of that product and also show the total quantity ordered by that customer.

I came up with following query, to list the max quantity product per order:

SELECT o1.OrderID, o1.ProductID, SUM(o1.OrderedQuantity) AS A
FROM OrderLine_T o1
GROUP BY
  o1.ProductID,
  o1.OrderID
HAVING SUM(o1.OrderedQuantity) = (
    SELECT MAX(s.d)
    FROM (
        SELECT
          o1.OrderID,
          o1.ProductID,
          SUM(o1.OrderedQuantity) AS d
        FROM OrderLine_T o1
        GROUP BY
          o1.ProductID,
          o1.OrderID
    ) s
    WHERE o1.ProductID = s.ProductID
)

And that gave me a correct output of:

50  20  1
48  17  5
32  14  10
59  13  2
1   10  9
2   8   2
69  7   4
4   6   3
32  5   10
55  4   2
2   3   12
1   2   18
26  1   5

But then, when I tried joining it with other tables, so I could select CustomerName and CustomerID, like so:

SELECT
  o1.ProductID,
  s.CustomerName,
  s.CustomerID,
  SUM(o1.OrderedQuantity) AS A
FROM OrderLine_T o1
INNER JOIN (
    SELECT
      c1.CustomerName,
      c1.CustomerID,
      p1.ProductID
    FROM Product_T p1
    INNER JOIN OrderLine_T o3 ON p1.ProductID = o3.ProductID 
    INNER JOIN Order_T o2 ON o3.OrderID = o2.OrderID
    INNER JOIN Customer_T c1 ON o2.CustomerID = c1.CustomerID
) s ON s.ProductID = o1.ProductID
GROUP BY
  o1.ProductID,
  s.CustomerName,
  s.CustomerID
HAVING SUM(o1.OrderedQuantity) = (
    SELECT MAX(s.d)
    FROM (
        SELECT
          o1.OrderID,
          o1.ProductID,
          SUM(o1.OrderedQuantity) AS d
        FROM OrderLine_T o1
        GROUP BY
          o1.ProductID,
          o1.OrderID
    ) s
    WHERE o1.ProductID = s.ProductID
) ;

The output shrunk to:

17  Contemporary Casuals    1   5
8   Home Furnishings        3   2
7   Eastern Furniture       4   4
10  Eastern Furniture       4   9
20  Dunkins Furniture       8   1
13  Ikards                  13  2

Why could that be?

CodePudding user response：

It seems you should be using window functions here, such as ROW_NUMBER, along with conditional aggregation

SELECT
  o.ProductID,
  p.Description,
  CustomerID = MAX(CASE WHEN o.rn = 1 THEN c.CustomerID END),
  CustomerName = MAX(CASE WHEN o.rn = 1 THEN c.CustomerName END),
  SUM(CASE WHEN o.rn = 1 THEN o.TotalQty END) AS QtyForTopCustomer
  SUM(o.TotalQty) AS TotalQty
FROM (
    SELECT
      o.ProductID,
      o.CustomerID,
      TotalQty = SUM(oi.OrderedQuantity),
      rn = ROW_NUMBER() OVER (PARTITION BY oi.ProductId ORDER BY SUM(oi.OrderedQuantity) DESC)
    FROM OrderLine_T ol
    INNER JOIN Order_T o ON o.OrderID = ol.OrderID
    GROUP BY
      o.ProductID,
      o.CustomerID
) o
INNER JOIN Customer_T c ON c.CustomerID = o.CustomerID
INNER JOIN Product_T p ON p.ProductID = ol.ProductID
GROUP BY
  o.ProductID,
  p.Description;

If you only wanted the data for that one customer, you could remove the conditional aggregation and just filter by row-number

SELECT
  o.ProductID,
  p.Description,
  o.CustomerID,
  o.CustomerName,
  o.TotalQty
FROM (
    SELECT
      p.ProductID,
      p.Description,
      o.CustomerID,
      TotalQty = SUM(oi.OrderedQuantity),
      rn = ROW_NUMBER() OVER (PARTITION BY oi.ProductId ORDER BY SUM(oi.OrderedQuantity) DESC)
    FROM OrderLine_T ol
    INNER JOIN Order_T o ON o.OrderID = ol.OrderID
    GROUP BY
      p.ProductID,
      p.Description,
      o.CustomerID
) o
INNER JOIN Customer_T c ON c.CustomerID = o.CustomerID
INNER JOIN Product_T p ON p.ProductID = ol.ProductID
WHERE o.rn = 1;