# How to create a Virtual layer with point geometry and count repetitions

From a table with the following fields:

CODE1 CODE2 XCOORD YCOORD
162 6605548EG7180N0001SF 418596,65 4620206,74
196 6604802EG7180S0001SX 418477,91 4620364,53
196 6604802EG7180S0001SX 418477,91 4620364,53
196 6604802EG7180S0001SX 418477,91 4620364,53
271 6605571EG7180N0001MF 418505,91 4620339,07
347 6605569EG7180N0001OF 418513,25 4620328,71
415 6605566EG7180N0001TF 418523,92 4620312,81
663 6604801EG7180S0001EX 418473,32 4620369,35
713 6604832EG7180S0002KM 418593,37 4620192,22
713 6604832EG7180S0002KM 418593,37 4620192,22
1128 6605547EG7180N0009AX 418603,38 4620193,32
1626 6604806EG7180S0001WX 418491,55 4620344,03
1695 6604830EG7180S0001XX 418583,67 4620205,61
1757 6605551EG7180N0001SF 418572,92 4620238,87
1757 6605551EG7180N0001SF 418572,92 4620238,87
1757 6605551EG7180N0001SF 418572,92 4620238,87
1757 6605551EG7180N0001SF 418572,92 4620238,87
1757 6605551EG7180N0001SF 418572,92 4620238,87
1757 6605551EG7180N0001SF 418572,92 4620238,87

I would like to create a virtual layer that does the following:

1- Group by the field 'CODE1'

2- Draw a point geometry at coordinates 'XCOORD' and 'YCOORD'

3- Count the number of repetitions by 'CODE1'

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

CODE1 CODE2 REPETITIONS
162 6605548EG7180N0001SF 1
196 6604802EG7180S0001SX 3
271 6605571EG7180N0001MF 1
347 6605569EG7180N0001OF 1
415 6605566EG7180N0001TF 1
663 6604801EG7180S0001EX 1
713 6604832EG7180S0002KM 2
1128 6605547EG7180N0009AX 1
1626 6604806EG7180S0001WX 1
1695 6604830EG7180S0001XX 1
1757 6605551EG7180N0001SF 6

You can use `group by` and `count`...

Go to the menu `Layer > Add Layer > Add/Edit Virtual Layer...` and enter the following query. Replace `1234` by your CRS ID.

``````SELECT code1, code2, count(*) as REPETITIONS, MakePoint(XCOORD,YCOORD,1234) as geometry
FROM myLayer
GROUP BY code1, code2
``````

PS: Depending how the primary key is defined, you might have to use `min(xcoord)` and `min(ycoord)`

• Could you give more details in which conditions it is recommended to use the functions `min(xcoord)` and `min(ycoord)`, and could you add an example of the virtual layer query? Commented Apr 2 at 12:54
• sqlite.org/…
– JGH
Commented Apr 2 at 15:06
• Very useful, thank you Commented Apr 4 at 11:32