nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Vind meer berichten getagd met

Accepted answers

jcarlson

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.

All comments

Anonymous User

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

bixb0012

@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".

Anonymous User

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

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.

Anonymous User

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

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)

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.

Anonymous User

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!

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?

DougGreen

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 ''.

jcarlson

That doesn't look like SQL to me.

(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.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.