MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `shipment_locations` (
  `id` int(6) primary key,
  `name` varchar(200) NOT NULL
);
INSERT INTO `shipment_locations` (`id`, `name`) VALUES
  ('1', 'france'),
  ('2', 'usa'),
  ('3', 'spain'),
  ('4', 'germany');
  
CREATE TABLE IF NOT EXISTS `purchase_requests` (
  `id` int(6) primary key,
  `name` varchar(200) NOT NULL,
  `total_cost_confirmed` int(6) NULL,
  `shipment_location_id` int(6) NULL,
 FOREIGN KEY (`shipment_location_id`) REFERENCES `shipment_locations` (`id`)
);
INSERT INTO `purchase_requests` (`id`, `name`, `total_cost_confirmed`, `shipment_location_id`) VALUES
  ('1', 'pr1', '109', 1),
  ('2', 'pr2', '1500', 3),
  ('3', 'pr3', '3000', 2),
  ('4', 'pr4', '10000', 2),
  ('5', 'pr5', '5', 3),
  ('6', 'pr6', '3000', 2),
  ('7', 'pr7', '3000', 2),
  ('8', 'pr8', '1', 1),
  ('9', 'pr9', '10000000', 3);
# Want this result (totalConfirmed needs to be from shipment location)
# id	name	shipment_location_id	totalConfirmed
# 4	    pr4	    2	                    19000
# 3	    pr3	    2	                    19000
# 1	    pr1	    1	                    110
# 8	    pr8	    1	                    110
SELECT p.id, p.name, p.shipment_location_id, t.totalConfirmed
FROM purchase_requests p 
INNER JOIN (
  SELECT shipment_location_id, SUM(total_cost_confirmed) totalConfirmed 
  FROM purchase_requests
  GROUP BY shipment_location_id
) t ON t.shipment_location_id = p.shipment_location_id
WHERE p.id IN ('1', '3', '4', '8')
ORDER BY t.totalConfirmed DESC


id
name
shipment_location_id
totalConfirmed


4
pr4
2
19000

3
pr3
2
19000

8
pr8
1
110

1
pr1
1
110