sqldb2greatest-n-per-grouprow-number

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


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.


Solution

  • 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.