How to count something per specific criteria?

[Aleksandra]

Hello everyone,

I hope someone can help me with this one. What would be the best way to calculate a value based on specific criteria? I have a Department table and Resources table. I need to calculate number of employees in each department. I am using calculated value in Submission form, but I am not sure how to format the formula.

Many thanks,

Aleksandra

[cheonsa]

Hi @Aleksandra,

You can add WHERE criteria in your Select Statement:

SELECT COUNT(Employee) FROM TABLENAME WHERE Department = target.[@field:Department]

This will only count the number of Employees per Department.

 

Please note to change the Table name and the field names as well.  

[futurist]

Hi @Aleksandra,

 

You can use the COUNT function to grab the number for each table, like so:

SELECT COUNT(Field) FROM TableName

 

You'd simply have to change the TableName to your corresponding tables. If you're trying to get those numbers separately, you would have to separate them with two calculated values.

[Aleksandra]

Thank you for your response @futurist, but for some reason this is not working. Can you please take a look at the screenshot I attached? I am not sure where I am making a mistake. 

[futurist]

Hi @Aleksandra,

 

You're missing the SELECT at the beginning. Can you try adding that and see if thatll work? Also, it should be:

SELECT COUNT(Department) FROM Am_tbl_Resources_1

You don't need to wrap them in brackets. It's pretty much doing a SELECT query in SQL. You can read more about it here: https://www.w3schools.com/sql/sql_count_avg_sum.asp

[Aleksandra]

Thank you @futurist, it works. 

[Aleksandra]

This counts total number of employees actually, but I'll try to figure out how to divide them by department. Thank you for your help anyway.

[Aleksandra]

Thank you @cheonsa. I figured out that I need to add where criteria, but I wasn't sure about the syntax. This is very helpful.

[cheonsa]

23 minutes ago, Aleksandra said:

Thank you @cheonsa. I figured out that I need to add where criteria, but I wasn't sure about the syntax. This is very helpful.

You are welcome @Aleksandra!  

For more details, you can also check this article from Caspio's Online Help

https://howto.caspio.com/function-reference/

[GoodBoy]

Just an additional information, kindly note that SELECT statements and aggregate function like COUNT are not allowed in formula fields in the table level. You can only use them in calculated field or value inside the DataPages.