topic Re: Perform a SQL Count in Calculate Field in ArcGIS Pro Questions

Perform a SQL Count in Calculate Field

Anonymous User — Fri, 14 Jan 2022 16:50:28 GMT

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!

Re: Perform a SQL Count in Calculate Field

jcarlson — Fri, 14 Jan 2022 17:18:21 GMT

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.

Re: Perform a SQL Count in Calculate Field

DougGreen — Fri, 14 Jan 2022 17:32:50 GMT

Hi @Anonymous User, I'm thinking this isn't exactly a SQL question with a "def" statement. I think that's a Python function. You could use something like this as a field calculate function:

def getComplaintCount(complaint1, complaint2, complaint3, complaint4, complaint5, complaint6, complaint7, complaint8, complaint9, complaint10): compCount = 0 compCount += 1 if complaint1 != None else 0 compCount += 1 if complaint2 != None else 0 compCount += 1 if complaint3 != None else 0 compCount += 1 if complaint4 != None else 0 compCount += 1 if complaint5 != None else 0 compCount += 1 if complaint6 != None else 0 compCount += 1 if complaint7 != None else 0 compCount += 1 if complaint8 != None else 0 compCount += 1 if complaint9 != None else 0 compCount += 1 if complaint10 != None else 0 return compCount

This simply checks if each value is null and adds 1 to the compCount variable if it is not null. If your complaint fields are not nullable strings, you'd want to replace the "None" with a ''.

Re: Perform a SQL Count in Calculate Field

Anonymous User — Fri, 14 Jan 2022 18:05:29 GMT

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?

Re: Perform a SQL Count in Calculate Field

Anonymous User — Fri, 14 Jan 2022 18:13:03 GMT

Hello, this makes sense. I added this to the calculate field, but I get calculate expression is not valid.

I do see a functions field and count function is not in it. I wonder if this calculate field only performs the functions in this list?

Thank you Doug!

Re: Perform a SQL Count in Calculate Field

jcarlson — Fri, 14 Jan 2022 18:13:27 GMT

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.

Re: Perform a SQL Count in Calculate Field

DougGreen — Fri, 14 Jan 2022 18:16:12 GMT

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)

Re: Perform a SQL Count in Calculate Field

Anonymous User — Fri, 14 Jan 2022 18:21:37 GMT

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

Re: Perform a SQL Count in Calculate Field

jcarlson — Fri, 14 Jan 2022 18:29:13 GMT

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.

Re: Perform a SQL Count in Calculate Field

DougGreen — Fri, 14 Jan 2022 18:31:55 GMT

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.

Re: Perform a SQL Count in Calculate Field

jcarlson — Fri, 14 Jan 2022 18:37:38 GMT

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

Re: Perform a SQL Count in Calculate Field

Anonymous User — Fri, 14 Jan 2022 18:54:57 GMT

@jcarlson it worked! @DougGreen Thank you to both of you. Have a great weekend!

Re: Perform a SQL Count in Calculate Field

Anonymous User — Thu, 17 Feb 2022 23:46:32 GMT

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.

Re: Perform a SQL Count in Calculate Field

DougGreen — Fri, 18 Feb 2022 15:12:17 GMT

Hi @Anonymous User, I don't know why I didn't pick up on this before but of course you're wanting this to happen regularly. This solution that was provided here works whenever you go in and calculate the field. So Calculating a field in this way is a "one-and-done" type of thing. It's not setting up an automatic default value of sorts on the field. So as new records are added or any information changes on the record, you'd have to calculate the field to show the changes. I don't think you're going to want to do that regularly but you could set up a minimal python script to run once or twice a day that would calculate that field for you https://pro.arcgis.com/en/pro-app/2.8/tool-reference/data-management/calculate-field.htm.

Re: Perform a SQL Count in Calculate Field

JoshuaBixby — Fri, 18 Feb 2022 17:11:36 GMT

@Anonymous User, on an unrelated note, using "no" as an abbreviation for "number" in a field name isn't a good practice. For example your current field name is entertotalnocomplaints, which is intended to be read as "enter total number of compliants," but could be read as "enter total of no (or non) complaints." Also, the field does not appear to be entered by the user directly so it is a bit confusing to start it with "enter." I suggest a field name of "NumComplaints" or "ComplaintCount".

Re: Perform a SQL Count in Calculate Field

Anonymous User — Fri, 18 Feb 2022 17:16:39 GMT

Thank you! I am learning that certain words do not belong in field names. Great point.