2

In QGIS 2.18, there are two layers, 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 a Virtual Layer 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. What can be a problem?

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

Moreover, I would like to know if I am eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON.


References:

1

A bit of theory

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 of the inner table which is (in general) invisible out of the subquery.


Solution

SELECT *
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

Reference:

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.