Vjylku

Pure Functions: Does "No Side Effects" Imply "Always Same Output, Given Same Input"?

Levi-Civita symbol: 3D matrix

Source for Cremation Specifically Not Jewish

I can't die. Who am I?

Is there any relevance to Thor getting his hair cut other than comedic value?

Misplaced tyre lever - alternatives?

Test pad's ESD protection

Is the withholding of funding notice allowed?

Borrowing Characters

The need of reserving one's ability in job interviews

Rationale to prefer local variables over instance variables?

What is better: yes / no radio, or simple checkbox?

When was drinking water recognized as crucial in marathon running?

Citing contemporaneous (interlaced?) preprints

For a 1-action spell, do I need to take a turn to ready the spell before I can cast it, or can I cast it immediately?

Every subset equal to original set?

Does "legal poaching" exist?

How can I be pwned if I'm not registered on the compromised site?

What does @RC mean in SSDT SQL Server Unit Testing?

Why is it "take a leak?"

Is there a full canon version of Tyrion's jackass/honeycomb joke?

Sometimes a banana is just a banana

Why do phishing e-mails use faked e-mail addresses instead of the real one?

How to lift/raise/repair a segment of concrete slab?

Creating Virtual Layer as a UNION of two LEFT JOINs in QGIS

QGIS virtual layer path never saved as relativeUse multiple connections/databases in a query layer?Creating new Virtual Layer programmatically in QGIS?How to save a virtual layer (from a filtered join) with features from original layer in QGIS?Creating virtual layers using the processing toolboxSpatial Join of Polygons, many to manyUsing WFS layers in Virtual Layer crashing/too slow QGISQGIS: alternative to add/edit virtual layer. With python?Creating a fixed distance buffer from a virtual layerQGIS Server Adding a virtual layer

1

In QGIS 2.18 I have 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...

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:

• Two SQL LEFT JOINS produce incorrect result


• Multiple left joins on multiple tables in one query

attribute-joins qgis-2 virtual-layer

share|improve this question

edited 8 mins ago

Taras

asked 16 hours ago

TarasTaras

2,1602726

add a comment | 

1

In QGIS 2.18 I have 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...

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:

• Two SQL LEFT JOINS produce incorrect result


• Multiple left joins on multiple tables in one query

attribute-joins qgis-2 virtual-layer

share|improve this question

edited 8 mins ago

Taras

asked 16 hours ago

TarasTaras

2,1602726

add a comment | 

In QGIS 2.18 I have 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...

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:

• Two SQL LEFT JOINS produce incorrect result


• Multiple left joins on multiple tables in one query

attribute-joins qgis-2 virtual-layer

share|improve this question

edited 8 mins ago

Taras

asked 16 hours ago

TarasTaras

2,1602726

Id | Dimension

---------------    

 1 |    4

 2 |    8

 3 |    2

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

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

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

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

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

share|improve this question

edited 8 mins ago

Taras

asked 16 hours ago

TarasTaras

2,1602726

share|improve this question

edited 8 mins ago

Taras

asked 16 hours ago

TarasTaras

2,1602726

asked 16 hours ago

TarasTaras

2,1602726

asked 16 hours ago

TarasTaras

2,1602726