7

Is there a way to fill the table with a row number so that count will restart as new group (identified by a separate column) starts? As a result, "row number" column will restart the count when the new attribute value for a group is found, and will produce row number (1,2,3 etc.) record within this group.

This post on stack overflow seems to answer my question, but SQL in QGIS does not allow such statements (DENSE_RANK) (or it does but I don't know how?).

1
  • Could you mention the QGIS version you used so that it helps others facing similar problems.
    – mapperx
    Oct 13, 2018 at 5:22

4 Answers 4

7

Do you need to use a field calculator? If not, the "Add autoincremental field" algorithm from the processing toolbox does what you want including the grouping.

autoincremental


References:

1
  • I had a similar problem on QGIS version 2.18.16. I needed to auto increment value using Group value as a criteria. I was able to do it using QGIS3.0.3 On the older version QGIS 2.18.16 the group value option is not present.
    – mapperx
    Oct 13, 2018 at 5:21
7

Credits to @Gabriel De Luca from his answer https://gis.stackexchange.com/a/364575/99589

In the Field Calculator use the following expression:

array_find(array_agg($id, "group"), $id) + 1

where "group" is an attribute that will restart the count when a new group appears.

result

6

If you know the row number of the beginning of each group, you can create a group_row_number field in the Field Calculator with a simple case when statement.

In my example, I have groups A, B, C and D. Group A is rows 1-4. Group B is rows 5-8. Group C is rows 9-12. Group D is rows 13-17.

Field Calculator expression: enter image description here

Result:

enter image description here

The generalized formula is:

case
  when "groupfieldname" = 'first group name' then @row_number
  when "groupfieldname" = 'second group name' then @row_number - [final_row_number_of_first_group]
  when "groupfieldname" = 'third group name' then @row_number - [final_row_number_of_second_group]
  ...
end
1
  • Thanks, that works, although I was expecting that there is a cleaner solution.
    – Ruslan
    Jun 13, 2018 at 20:00
4

You can also do this with QGIS 3 field calculator more easy:

array_find(array_agg("GROUPFIELDNAME"||'_'||-$id,"GROUPFIELDNAME"),"GROUPFIELDNAME"||'_'||-$id)+1

Replace "GROUPFIELDNAME" with the name of your field you want to group by.

2
  • can you explain it, please? I used that and it worked. However I can't understand "| |". Mar 16, 2023 at 23:16
  • || is a concatenator. You could also use concat(a,b) instead.
    – MrXsquared
    Mar 17, 2023 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.