1

I am trying to join the grouped sum of two layers using virutal layers in QGIS 3.2.

My query looks like:

select nvewi.zone as "Zone-Type", sum(nvewi.bev) as "Inhabitants Isochrones", sum(nvewp.bev) as "Inhabitants Puffer" 
from nvewi 
inner join nvewp 
on nvewi.zone=nvewp.zone 
group by nvewi.zone

Table nvewi:

bev zone
50  a
20  a
30  a
10  b
20  b
30  b
2   c
2   c

Table nvewp:

bev zone
10  a
10  a
20  a
5   b
3   b
2   b
1   c
1   c

The result I get is obviously wrong:

Zone-Type   Inhabitants Isochrones  Inhabitants Puffer
a   300 120
b   180 30
c   8   4

The result how it should be:

Zone-Type   Inhabitants Isochrones  Inhabitants Puffer
a   100 40
b   60  10
c   4   2

Where is my mistake? And how to perform this join correctly getting the correct sums?

3

The correct SQL writing for this is :

SELECT t1.zone AS "Zone-Type",
  t1."Inhabitants Isochrones",
  t2."Inhabitants Puffer"
FROM (SELECT nvewi.zone,
        SUM(nvewi.bev) AS "Inhabitants Isochrones"
      FROM nvewi
      GROUP BY nvewi.zone) t1
INNER JOIN (SELECT nvewp.zone,
              SUM(nvewp.bev) AS "Inhabitants Puffer"
            FROM nvewp
            GROUP BY nvewp.zone) t2
ON t1.zone = t2.zone

To understand, make your query without the group by and sum clauses :

select nvewi.zone as "Zone-Type",
nvewi.bev as "Inhabitants Isochrones",
nvewp.bev as "Inhabitants Puffer" 
from nvewi 
inner join nvewp 
on nvewi.zone=nvewp.zone

This return 22 records, whose sums gives the "incorrect" results.

| improve this answer | |
  • Perfect solution! And thanks for the explanation. – MrXsquared Jul 16 '19 at 15:15
2

The sums are correct, because joining

bev zone
50  a
20  a
30  a

and

bev zone
10  a
10  a
20  a

... will yield t1 x t2:

t1.bev zone t2.bev
50 a 10
50 a 10
50 a 20
20 a 10
20 a 10
20 a 20
30 a 10
30 a 10
30 a 20
10 b 5
10 b 3
10 b 2
20 b 5
20 b 3
20 b 2
30 b 5
30 b 3
30 b 2
2 c 1
2 c 1
2 c 1
2 c 1

So, it's always 3 times the expected output.

You need to sum before the join.

| improve this answer | |
  • Was thinking about something like this. Thanks for pointing out and visualizing. – MrXsquared Jul 16 '19 at 15:16

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.