How to get Shipment method wise suppliers with most order quantity?

Question

I am having difficulties to understand when to use subqueries and how to implement them in a way that makes sense.

I have the following tables:

Table supplier:

supplier_id	name
1	Supplier#0000001
2	Supplier#0000002
4	Supplier#0000003
3	Supplier#0000004
...	...

Table orderitem:

order_id	shippingmethod	supplier
1	TRUCK	134
2	MAIL	345
3	REG AIR	223
4	REG AIR	11345
5	RAIL	344
...	...	535

I want to find the supplier with the highest amount of shipped order_items for each shipping method. So my end result should look this:

shippingmethod	suppliername	amount
TRUCK	Supplier#0000002	120
MAIL	Supplier#0003453	590
REG AIR	Supplier#0003456	433
AIR	Supplier#0000632	244
SHIP	Supplier#0000654	566

I came up with this:

SELECT 
    SHIPPINGMETHOD, SUPPLIERNAME, COUNT(ORDER_ID) AS AMOUNT
FROM 
    ORDERITEM
JOIN 
    SUPPLIER L ON L.SUPPLIER_ID = ORDERITEM.SUPPLIER
GROUP BY 
    SHIPPINGMETHOD, SUPPLIERNAME;

But this only returns the amount of shipped items of each supplier of each shipping method.

So then I tried this:

SELECT 
    SHIPPINGMETHOD, L.SUPPLIERNAME, COUNT(OI.ORDER_ID) AS AMOUNT
FROM 
    ORDERITEM AS OI
JOIN 
    SUPPLIER L ON L.SUPPLIER_ID = OI.SUPPLIER
GROUP BY 
    OI.SHIPPINGMETHOD, L.SUPPLIERNAME
HAVING 
    COUNT(OI.ORDER_ID) = (SELECT MAX(AMOUNT)
                          FROM 
                              (SELECT 
                                   OI2.SHIPPINGMETHOD, L2.SUPPLIERNAME, 
                                   COUNT(OI2.ORDER_ID) AS AMOUNT
                               FROM 
                                   ORDERITEM AS OI2
                               JOIN 
                                   SUPPLIER L2 ON L2.SUPPLIER_ID = OI2.SUPPLIER
                               GROUP BY 
                                   OI.SHIPPINGMETHOD, L.SUPPLIERNAME) AS SUB
);

I only get one row back. The row shows the correct supplier and the correct amount of shipped items for one of the methods, but I expected 5 rows. I feel like I am very close, but honestly have no clue at all what my mistake actually is.

Answer 1

If you are trying to find shipping method wise suppliers with highest order quantity then you can try this:

** here row_number()over() window function is used to rank the supplier in descending order of order quantity.

Query:

with cte as 
(
SELECT shippingmethod, L.name suppliername , COUNT(order_id) AS AMOUNT,
row_number()over(partition by shippingmethod, L.name order by COUNT(order_id) desc) as rn 
FROM orderitem
JOIN supplier L on L.supplier_id = orderitem.SUPPLIER
GROUP BY shippingmethod, L.name
)
select shippingmethod, suppliername , AMOUNT from cte
where rn=1

Output:

shippingmethod	suppliername	AMOUNT
MAIL	Supplier#0000002	1
RAIL	Supplier#0000004	1
REG AIR	Supplier#0000004	1
TRUCK	Supplier#0000001	1

fiddle

*** If there are more than one customer with highest order quantity all of those would be in the list.