Perform a SQL Count in Calculate Field

580
15
Jump to solution
01-14-2022 08:50 AM
AnnettePoole1
Occasional Contributor II

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!

 

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS

View solution in original post

15 Replies
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
AnnettePoole1
Occasional Contributor II

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?

AnnettePoole1_0-1642183301002.png

AnnettePoole1_1-1642183348296.png

 

0 Kudos
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
0 Kudos
DougGreen
Occasional Contributor

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)
AnnettePoole1
Occasional Contributor II

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

0 Kudos
jcarlson
MVP Honored Contributor

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

- Josh Carlson
Kendall County GIS
0 Kudos
jcarlson
MVP Honored Contributor

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

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

- Josh Carlson
Kendall County GIS
DougGreen
Occasional Contributor

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.

AnnettePoole1
Occasional Contributor II

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.

0 Kudos