MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Locations (id INT, dimension INT);
INSERT INTO Locations VALUES
(1,4),
(2,8),
(3,2);
CREATE TABLE Connections (origin INT, destination INT, value INT, distance_km INT);
INSERT INTO Connections VALUES
(1,2,500,30),
(1,3,100,20),
(2,1,100,10),
(2,3,300,10),
(3,1,100,40);
SELECT L.Id AS Id, L.Dimension AS Dimension, 
       sq1.In_Value, sq1.In_Count, sq1.In_Dist, 
       sq2.Out_Value, sq2.Out_Count, sq2.Out_Dist
FROM Locations AS L
LEFT JOIN (
           SELECT C.Destination, 
                  SUM(C.Value) AS In_Value,  
                  COUNT(C.Destination) AS In_Count,  
                  SUM(C.Distance_KM) AS In_Dist
           FROM Connections AS C
           GROUP BY C.Destination
         ) AS sq1 ON L.Id = sq1.Destination
LEFT JOIN (
           SELECT C.Origin,  
                  SUM(C.Value) AS Out_Value,  
                  COUNT(C.Origin) AS Out_Count,  
                  SUM(C.Distance_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
         ) AS sq2 ON L.Id = sq2.Origin;


Id
Dimension
In_Value
In_Count
In_Dist
Out_Value
Out_Count
Out_Dist


1
4
200
2
50
600
2
50

2
8
500
1
30
400
2
20

3
2
400
2
30
100
1
40