0

I have two tables, e.g. "Locations" and "Connections"

"Locations" has values

Id | Dimension
---------------    
 1 |    4
 2 |    8
 3 |    2

"Connections" maintains attributes

Origin | Destination | Value | Distance_KM
-------------------------------------------
   1   |      2      |  500  |     30
   1   |      3      |  100  |     20
   2   |      1      |  100  |     10
   2   |      3      |  300  |     10
   3   |      1      |  100  |     40

I want to create an output with the following Attribute Table. Where "In" correspond to "Destination" from "Connections" and "Out" to "Origin" accordingly.

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

I can achieve the result that I strive for separately with two queries.

Query 1

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

Query 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

Nevertheless, there should be only one query that solves my issue, is not it? I tried this but no success.

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.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
        ) ON L.Id = C.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count, 
                  SUM(C.Origine_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
        ) ON L.Id = C.Origin

Basically, I do not know if I eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON, am I?


References:

  • 1
    Specify your DBMS, including version. – Akina Mar 6 at 7:09
  • I tried this but no success. Each subquery must have a separate alias unique within the whole query. ON clause must refer to a field with that alias, not a name iof inner table which is (in general) invisible out of the subquery. – Akina Mar 6 at 7:11
  • It is nothing to do with DBMS. I am working in a program that supports SQL. – Taras Mar 6 at 7:30
1
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

fiddle

  • Thanks! I did not know that there is even a Fiddle for SQL. – Taras Mar 6 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.