Using select by attributes in model builder to produce summary/sum table of selected attributes

[LiamO_Brien]

Hi all,

Hope all is well. I’m quite new to Model Builder, and I have been attempting to automize some of the work we do within ArcGIS Pro. Attached is an example of 1 type of material in which we need to count within ArcPro. Currently ‘Select by Attributes’ is used to select each individual line (so 6 select by attribute queries ran for this one material) e.g.;

Where 'status' = 1

And 'height' = 8.5

And 'build_clas' = C1

Ideally the result would have a table or excel with the sum values from each line. I have also attached the model builder I made but cant figure out the last part, which is a table stating the sum values from each query. Is this possible, or could someone provide some advice material that might point me in the correct direction?

Best regards,

Liam.

[KimGarbade]

Unless I'm missing something it looks like your writing your six queries against the same feature class.  I would use one query; something like this with your initial queries encased in parenthesis separated by "OR" statements (this example only has three queriers made into one):

((status = 1 AND height = 8.5 AND build_clas = 'C1') OR (status = 1 AND height = 9 AND build_clas = 'C2') OR (status = 2 AND height = 9.5 AND build_clas = 'C1'))

Then you would have to add a column to your output table to hold the sum (Add Field) and use the Calculate Field tool to populate it.

Hope I understood your question correctly and that this helps.

[LiamO_Brien]

Hi Kim, 

Thanks for getting back to me, unfortunately with that solution I will be left with 1 sum value for the 6 queries. Rather I require an output table stating the sum value of each individual query. Is this possible?

Liam. 

[KimGarbade · Accepted solution]

Sample data I had laying around is points (table view below): 

I wrote a quick model that takes the first 3 records above, writes them to another table and sums the "color" values for each row (I.E. Red, Yellow, etc).

1) First thing I did was create a blank  table in my database (not a feature class) that has the format I want in my output (in my case just the starting table with a field to hold the sum of the "Color" values).  This is the file I'm going to append the results of the "Select by Attributes" to and the table I'm going to "Calculate Field" against. Initially its empty.... see I just added a "ColorTotal" field to the schema of the input table, but yours can be more complex.

2) Next I wrote the sub-model.  Instead of writing 6 selects I used a "MultiValue" iterator and used its %value% as input into my "Select Layer By Attribute" tool. The results of that tool I appended to the table I created in step one (TestCal in my case).

3) The last step was to create the "Main" model that uses the sub-model created in step 2.  All this model does is uses the output of the sub model (the TestCalc table) as input into a "Calculate Field" tool.

The result being:

[LiamO_Brien]

Hi Kim, 

Thanks very much for the detailed response. It worked! Also the model you have presented seems far less cumbersome in comparison to the model I eventually got to work (I'll attached so you can see).

Liam. 

[KimGarbade]

No problem.  I'm glad it worked out.