A. Greensmith A. Greensmith - 1 year ago 70
SQL Question

Aggregate Function Confusion

I have a table with a list of products, schema is:

Product, Warehouse, Supplier, Order Quantity

What I want to do is for each supplier, select the warehouse that has the most order quantity.

Table Data:

Cornflakes, WH1, Kellogs, 10
Cornflakes, WH2, Kellogs, 5
Cornflakes, WH3, Kellogs, 0
Crunchy, WH1, Cadbury, 20
Crunchy, WH2, Cadbury, 10
Mars, WH1, Cadbury. 56
Mars, WH4, Cadbury, 8

I think there is enough information here to provide easy answer, if there is not please ask me for clarification in a comment rather than downvote and I will edit question very quickly.

Sample Output:

Kellogs, WH1
Cadbury, WH1

Warehouse number will be this one because it has the most total quantity to order for every product under that supplier.

Answer Source

MS SQL 2008+

SELECT Supplier, Warehouse
  SELECT Warehouse, Supplier, rn=ROW_NUMBER() OVER (PARTITION BY Supplier ORDER BY SUM([Order Quantity]) DESC)
  FROM t
  GROUP BY Warehouse, Supplier
) tt
WHERE rn=1