MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE supplier (
  supplier_id INT,
  name VARCHAR(255)
);


INSERT INTO supplier (supplier_id, name) VALUES (1, 'Supplier#0000001');
INSERT INTO supplier (supplier_id, name) VALUES (2, 'Supplier#0000002');
INSERT INTO supplier (supplier_id, name) VALUES (4, 'Supplier#0000003');
INSERT INTO supplier (supplier_id, name) VALUES (3, 'Supplier#0000004');

CREATE TABLE orderitem (
  order_id INT,
  shippingmethod VARCHAR(255),
  supplier INT
);


INSERT INTO orderitem (order_id, shippingmethod, supplier) VALUES (1, 'TRUCK', 1);
INSERT INTO orderitem (order_id, shippingmethod, supplier) VALUES (2, 'MAIL', 2);
INSERT INTO orderitem (order_id, shippingmethod, supplier) VALUES (3, 'REG AIR', 3);
INSERT INTO orderitem (order_id, shippingmethod, supplier) VALUES (4, 'REG AIR', 5);
INSERT INTO orderitem (order_id, shippingmethod, supplier) VALUES (5, 'RAIL', 3);

select * from supplier;
select * from orderitem;


supplier_id
name


1
Supplier#0000001

2
Supplier#0000002

4
Supplier#0000003

3
Supplier#0000004




order_id
shippingmethod
supplier


1
TRUCK
1

2
MAIL
2

3
REG AIR
3

4
REG AIR
5

5
RAIL
3


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


shippingmethod
suppliername
AMOUNT


MAIL
Supplier#0000002
1

RAIL
Supplier#0000004
1

REG AIR
Supplier#0000004
1

TRUCK
Supplier#0000001
1

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

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