Perform a SQL Count in Calculate Field

[Anonymous User]

Hello, 

This seems like it should be very easy, but I cannot get this to work. 

I have to use SQL because I have layers with sync or keep track of created and updated features enabled.

In a nutshell, I created a column titled 'entertotalnocomplaints'.  Note, this is a field map app the deputies are using. As of right now the deputies must count the number of complaints received on one address and then enter the total into this column. I feel I should be able to count the number of complaint fields that have text and return that total count to the field 'entertotalnocomplaints'.  So count those with text and return a numeric number to the 'entertotalnocomplaints' field. 

Upon some research of this forum this is what I have come up with so far, obviously it doesn't work. I would really appreciate some help. Thank you.

SQL statement that I put in the calculate field

entertotalnocomplaints =

def FieldCount(complaint, complaint1, complaint2, complaint3, complaint4, complaint5, complaint6, complaint7, complaint8, complaint9, complaint10):
fields = [complaint, complaint1, complaint2, complaint3, complaint4, complaint5, complaint6, complaint7,  complaint8, complaint9, complaint10]
return count

Thank you!

[jcarlson · Accepted solution]

That doesn't look like SQL to me.

It's hard to know how to write the expression without knowing about the underlying data structure, but if you've got 10 complaint fields, and you want to increment the count for each field with a value in it, try something like this:

(CASE WHEN CHAR_LENGTH(complaint1) > 0 THEN 1 ELSE 0 END) +
(CASE WHEN CHAR_LENGTH(complaint2) > 0 THEN 1 ELSE 0 END) +
... etc ... +
(CASE WHEN CHAR_LENGTH(complaint10) > 0 THEN 1 ELSE 0 END)

 Probably not the most elegant approach, but SQL's not my primary language.

[Anonymous User]

Hello - thank you so much for your help. 

Info - I created a Field Map app for our deputies to use. I have a feature layer with several fields and my thought is to use the calculate field to perform a count on specific columns. Basically counting the field, if there is text, count it as 1, add the counts and enter total count in my "Enter Total Number of Complaints" field.

I am in the Enterprise, go to the layer, go to data, find my field "entertotalnumberofcomplaints", click calculate on that field.  And based on the image below, I must use SQL.  The complaint fields are string and the 'enter total complaint number' field is numeric. Does that help?

[jcarlson]

Makes sense, thank you for elaborating. Did you try the expression I posted? It worked in a quick test I did.

I believe if you run the field calculation in ArcGIS Pro or in a Python environment, you have access to other calculation methods, if that's useful to you.

[DougGreen]

Oh, ok. You might be able to pull the feature class into Pro and perform a field calculation on it using the previous python but i think @jcarlson had the right idea then. Only catch there is that if any of the values are NULL, you would get a NULL. So you could use this to change any empty strings to NULL and add 1 if not NULL.

IIF(NULLIF(complaint1, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint2, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint3, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint4, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint5, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint6, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint7, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint8, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint9, '') IS NULL, 0, 1) +
IIF(NULLIF(complaint10, '') IS NULL, 0, 1)

[Anonymous User]

Makes sense. So is this SQL or python that you and JCarlson are providing me? SQL?

[jcarlson]

Refer to this documentation to see what can/can't be done in AGOL SQL queries:

https://doc.arcgis.com/en/arcgis-online/reference/sql-agol.htm

[jcarlson]

Enclosing the CHAR_LENGTH function in a CASE will evaluate a 0 in the event of a null field.

And @Anonymous User  my expression was SQL, sorry for not specifying.

[DougGreen]

Yep, don't go off of mine ;). That was T-SQL for SQL. Didn't realize it was only a subset of SQL in that window.

[Anonymous User]

Hello again! As mentioned I have a field I am using for calculate.  When I input your solution, it works. The number appears in the field correctly.  But now I discovered, when I added data via the Field Map or Collector, the calculate field is not updating. So I went back, and re entered it and it worked. It updated, but still doesn't update when data is input to the app. Strange. Any ideas why? I searched but do not see any.