Wrong result using virtual layer sum with join and group by

Question

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?

Answer 1

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.

Answer 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.