# How to calculate sum for unique values in a model?

I have two layers. A layer represented by a fishnet and a layer represented by certain boat routes. After I intersect them I want to be able to calculate the sum and max value for certain characteristics like dead weight, gross tonnage and beam per each square from the fishnet per each track.
Here is an example of how my table looks like after I intersect the fishnet with the boat tracks:

``````FID_Sample  FID_FishGr  mmsi    Beam    Gross_Tonn  Deadweight
0   7017    440296000   8   764 394
1   7017    440296000   8   764 394
2   7017    440761000   12  812 588
3   7017    440761000   12  812 588
4   7017    440980000   10  788 391
5   7017    440980000   10  788 391
6   7017    441053000   10  811 455
7   7017    441053000   10  811 455
``````

As you can see in each square(FID_FishGr), there are a certain number of tracks(mmsi) and each track has it's own characteristics of Beam, Gross_Tonn and Deadweight.
Now I have created a model, hoping that I have solved my problem nice and easy, but when I checked the math in excel it weren't the results I expected.

When I use the model posted above, and I want to calculate the SUM for Gross Tonnage in each FID_FishGr it gets me the sum of all boat tracks even if they are duplicate(more lines of the same track in a square).

``````OBJECTID    FID_FishGr  FREQUENCY   SUM_Gross_
1   7017    8   6350
2   7018    2   1576
3   7019    1   788
4   7183    8   6350
5   7184    8   6350
6   7185    5   3987
7   7186    3   2411
8   7349    8   6350
9   7350    8   6350
``````

Lets take a clear example: - in the first table you can see the tracks in one entire square and if you add the gross tonnage for each track you will get 3175 but if I sum it up with summary statistic I get 6350, as you can see in the second table.

What I want to achieve, is to calculate a SUM for each unique square of the fishnet using only unique track boat and not to sum up all of them.

What tool should I use to do this and how?

• Just a thought, since it is unclear to me from your question. You mentioned that there are duplicated routes ineach square - how come? Anyway if those are "perfect duplicates", i.e. also having the same geometry, you might want to to use 'Delete Identical' tool and set it to the Shape (i.e. geometry) field. It will remove all duplicates, leaving you with one unique copy for each intersected feature – dof1985 Jul 16 '15 at 21:21

## 1 Answer

You need to do a dissolve before the sum, to amalgamate the duplicates into one row. This would be because a track zigzags over the boundary so after intersect there's multiple instances = multiple lines.

Dissolve will only work for feature classes, not tables, so it must be done just after the intersect. If you have a table then summarize with summary statistics (count OID/FID, as the tool needs at least 1 summary field and case fields of all the fields you'll want to sum later):

``````FID_Sample  FID_FishGr  mmsi    Beam    Gross_Tonn  Deadweight
0   7017    440296000   8   764 394
1   7017    440296000   8   764 394
2   7017    440761000   12  812 588
3   7017    440761000   12  812 588
4   7017    440980000   10  788 391
5   7017    440980000   10  788 391
6   7017    441053000   10  811 455
7   7017    441053000   10  811 455
``````

``````Beam    Gross_Tonn  Deadweight  FREQUENCY   COUNT_FID_
8   764 394 2   2
10  788 391 2   2
10  811 455 2   2
12  812 588 2   2
``````

``````FREQUENCY   SUM_Beam    SUM_Gross_  SUM_Deadwe
4   40.00000000000  3175.00000000000    1828.00000000000
``````

Which is the 3175 you expected from Excel.

• Thank you Michael. This resolved my problem. I was not expecting to be that simple. Thanks again. – alecsx Jul 17 '15 at 12:27