2

From 3 tables with the following fields:

PARENT_TABLE

CODE1 CODE2 XCOORD XCOORD
12395 8604813EG7180S0001GX 418477,91 4620364,53
13505 8604813EG7180S0001GX 418477,91 4620364,53
12968 8604813EG7180S0001GX 418477,91 4620364,53
12758 8604813EG7180S0001GX 418505,91 4620339,07
13208 8605549EG7180N0004QJ 418513,25 4620328,71
13210 8605549EG7180N0004QJ 418523,92 4620312,81
12284 8603404EG7180S0014LO 418473,32 4620369,35
18436 8703306EG7180S0009MU 418593,37 4620192,22
16674 8605547EG7180N0006IL 418603,38 4620193,32
16678 8605547EG7180N0006IL 418491,55 4620344,03
16679 8605547EG7180N0006IL 418583,67 4620205,61
16680 8605547EG7180N0006IL 418572,92 4620238,87
16684 8605547EG7180N0006IL 418572,92 4620238,87
16693 8605570EG7180N0001FF 418572,92 4620238,87
19620 8605570EG7180N0001FF 418572,92 4620238,87
18473 8605570EG7180N0001FF 418572,92 4620238,87
18489 8605550EG7180N0001EF 418572,92 4620238,87
16722 8604822EG7180S0001MX 418672,92 4621238,87

CHILD_TABLE1

CODE1
12395
12395
12395
12395
13505
13505
13505
12968
12758
13208
13210
12284
12284
12284
18436
18436
18436
18436

CHILD_TABLE2

CODE1
16678
16678
16678
16678
16678
16680
16680
16680
16680
16680
16680
16680
16680
16680
16680
16680
16680
16680

I would like to obtain a virtual layer with point geometry that does the following:

1- Draw a points layer at the coordinates 'XCOORD' and 'YCOORD' of the PARENT_TABLE

2- Create the field 'REPETITION_PARENT' with the number of times the field 'CODE2' from PARENT_TABLE is repeated

3- Create the field 'REPETITION_CHILD1' with the number of times the field 'CODE1' from CHILD_TABLE1 is repeated

4- Create the field 'REPETITION_CHILD2' with the number of times the field 'CODE1' from CHILD_TABLE2 is repeated

For example, for the resulting layer with point geometry, the table structure would be:

CODE1 CODE2 XCOORD XCOORD REPETITION_FATHER REPETITION_CHILD1 REPETITION_CHILD2
12395 8604813EG7180S0001GX 418477,91 4620364,53 4 4 0
13505 8604813EG7180S0001GX 418477,91 4620364,53 4 3 0
12968 8604813EG7180S0001GX 418477,91 4620364,53 4 1 0
12758 8604813EG7180S0001GX 418505,91 4620339,07 4 1 0
13208 8605549EG7180N0004QJ 418513,25 4620328,71 2 1 0
13210 8605549EG7180N0004QJ 418523,92 4620312,81 2 1 0
12284 8603404EG7180S0014LO 418473,32 4620369,35 1 3 0
18436 8703306EG7180S0009MU 418593,37 4620192,22 1 4 0
16674 8605547EG7180N0006IL 418603,38 4620193,32 5 0 0
16678 8605547EG7180N0006IL 418491,55 4620344,03 5 0 5
16679 8605547EG7180N0006IL 418583,67 4620205,61 5 0 0
16680 8605547EG7180N0006IL 418572,92 4620238,87 5 0 13
16684 8605547EG7180N0006IL 418572,92 4620238,87 5 0 0
16693 8605570EG7180N0001FF 418572,92 4620238,87 3 0 0
19620 8605570EG7180N0001FF 418572,92 4620238,87 3 0 0
18473 8605570EG7180N0001FF 418572,92 4620238,87 3 0 0
18489 8605550EG7180N0001EF 418572,92 4620238,87 1 0 0
16722 8604822EG7180S0001MX 418672,92 4621238,87 1 0 0

Solution I tried:

SELECT 
  t1.CODE1, 
  t2.CODE2, 
  COUNT(*) AS REPETITIONS,
  MakePoint(XCOORD,YCOORD,25831) as geometry
FROM PARENT_TABLE t1
JOIN CHILD_TABLE1 t2 ON t1.CODE1 = t2.CODE1
GROUP BY t1.CODE1, t2.CODE2
ORDER BY REPETITIONS DESC
Improve this question
0

1 Answer 1

Reset to default
1

CODE2 in the PARENT_TABLE is distributed over multiple records with different values for CODE1, so a simple GROUP BY will not work (although it might work with a separate subquery). Instead, the following example uses a window function in a subquery on PARENT_TABLE to collect the number of instances for CODE2, regardless of the values of CODE1 they appear with.

The result is then left-joined with both child tables to collect their respective counts. As follows:

SELECT parent.*
, count(child_table1.code1) AS repetition_child1
, count(child_table2.code1) AS repetition_child2
FROM (
  SELECT code1
  , MakePoint(XCOORD,YCOORD,25831) as geometry
  , COUNT(*) OVER (PARTITION BY code2) AS repetition_father
  FROM parent_table
) AS parent
LEFT JOIN child_table1 ON parent.code1 = child_table1.code1
LEFT JOIN child_table2 ON parent.code1 = child_table2.code1
GROUP BY parent.code1, geometry, parent.repetition_father
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.