# Field Calculator - Calculating Sum based on Different Fields

I'll explain this the best I can. I have 3 fields that I need to work with in Field Calculator:

• DesignLength
• AsBuiltLength

DesignLength is length of each line feature calculated by ArcMap. Each LinkID instance has its own specific DesignLength. However, there are ID numbers that are used multiple times. What I need to do is sum the DesignLength for one number (that could have multiple rows) into the AsBuiltLength field using Field Calculator.

Here is a screenshot of my table.

So for example, 0102 would have the same number in both DesignLength and AsBuiltLength because there is only one instance of 0102. But for 0104, I need all 13 instances of 0104 to be added together, and then that same number to be put in AsBuiltLength in all 13 rows for 0104.

Any ideas? I'm struggling to get anywhere successful in Field Calculator.

Its way easier with another approach. You can use summary statistics within the attribut table. Right-click on your Link-ID field -> Summary Statistics and then choose SUM as statistics for your DesignLength column. Safe the new table as table of your database and use a JOIN to add your calculated columns back to your original table. This way the sum-values are written to each row.

If you have LibreOffice installed and you promise not to do sorting or moving any rows up or down, then:

1. Close QGIS
2. Open the .dbf file of your shapefile(s) in LibreOffice Calc
3. Choose charset.. it's usually UTF-8
5. Copy the derived values column
6. Paste Special (`Ctrl+Shift+V`) to a new column, with only text/numbers selected and formulas de-selected
7. Get rid of the formulas column
8. Look at the other column headers to name this one so QGIS will recognize it. All uppercase, and suffixed with `,C,5` or `,N,2,0` or so

8.1. `,C,5` denotes text column of max length 5 chars.

8.2. `,N,2,0` denotes number with no decimals. I have seen a `,N,19,11` used for holding large floating point values. The last number is the number of decimals allowed. Take your call on what to do here.

1. Save changes to your .dbf file and close LibreOffice.
2. Open the shapefile in QGIS again, and you should be able to see the updated metadata.

Precautions:

• Make a backup copy of your .dbf before editing in case you mess up.
• Do NOT sort or re-arrange your rows, delete or add any rows etc. Shapefile doesn't index this data, it's all by sequence. It'll assign first row to first shape, second row to second shape etc. Column manipulations are ok - you can move those around.

Select by Attribute (ID), then calculate the sum for the design length = (x)

then for the same selected rows use the field calculator to input the value of (x)

repeat process for the remianing ID's

• I considered doing that, but there are over 1,700 ID's. – arowl24 Oct 12 '17 at 16:36
• use arcpy, do you have experience with python? – NULL.Dude Oct 12 '17 at 16:37
• I do, yes. I'm somewhat comfortable in it. So I'd need to use a for loop to run for each ID, but then how would I go about assigning a variable for each different design length? – arowl24 Oct 12 '17 at 16:47
• take a look at update cursor pro.arcgis.com/en/pro-app/arcpy/data-access/… – NULL.Dude Oct 12 '17 at 18:10