How do I Count Data in Fields?

[JckDY]

For example I have a Name Column, and I have entries such as: John, Jill, Jack.

 

I want to count How may John, Jill, and jack was there.

How to do that?

[JolliBeng]

Hi JckDY,

You can add Aggregations for that and use a select count query on your formula:

SELECT COUNT(columnname) FROM tablename WHERE columnname='John'

 

Hope this helps!

 

-JolliBeng

[Role21]

is there a CASE statement I can write in an aggregate field to count unique values of a field?

[JolliBeng]

Hi Role21,

You can use this query for your aggregation:

SELECT COUNT(DISTINCT column_name) FROM table_name

 

 

-JolliBeng

[techguy]

any way I can do a Select count statement within an html datapage?

[Vitalikssssss]

4 hours ago, techguy said:

any way I can do a Select count statement within an html datapage?

You can use SQL statements in Calculated, Total&Aggregation fields and also if you assign Calculated value form element to a field from submission form.

Regards,

vitalikssssss

[AtayBalunbalunan]

Below are some documentations that can help you with this:

1. Calculations in Reports - https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/ 
   • Here you can find an example for using Subqueries in Calculated Fields. Calculated fields also support the SELECT statement to get values from the same or other tables.
2. Calculated Values - https://howto.caspio.com/datapages/datapage-components/calculated-values/

 

On 11/29/2018 at 9:09 PM, techguy said:

any way I can do a Select count statement within an html datapage?

[Kurumi]

Hi everyone! Just an update: Caspio has finally released a feature to show DISTINCT records on Reports (Tabular/List/Gallery).

It is available in R30 version: https://howto.caspio.com/release-notes/caspio-30-0/

[Felix]

As an extension of this question. 

I want to create 'user friendly' IDs using names to create unique IDs. eg. John Smith would SMIJ01, and the second John Smith would be SMIJ02

Can anyone point me in the right direction as to how to do this with triggered actions?

 

Thanks!

[NailDyanC]

Hi just to add in the previous posts above, you may check all the functions that you can used in formula in this Caspio article: https://howto.caspio.com/function-reference/

[Queso]

Hi @Felix, this is what I use on my app to create a user friendly unique IDs. I utilize their Submission Form DataPage's Calculated Value which allows me create a formula that will combine the input field for Code (For example: JS derived from John Smith) and an ID (For example: 1).

 

Here is an example before submit: 

 

[Felix]

Thanks @trickson and @NailDyanC