clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582810 fiddles created (13409 in the last week).

CREATE TABLE Locations (id INT, dimension INT);
 hidden batch(es)


INSERT INTO Locations VALUES (1,4), (2,8), (3,2);
 hidden batch(es)


CREATE TABLE Connections (origin INT, destination INT, value INT, distance_km INT);
 hidden batch(es)


INSERT INTO Connections VALUES (1,2,500,30), (1,3,100,20), (2,1,100,10), (2,3,300,10), (3,1,100,40);
 hidden batch(es)


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
 hidden batch(es)