SQL - problem with SELECT AVG statement in Calculated Value / DataPage

[Johan]

 

I have encountered the following issue when using SQL statements in a Calculated Value in a DataPage:

I want to calculate average scores in a database based on the values in specific fields in the same record. These fields function as filters.

Example of record:

Company_Name              Company_Size_Employees                  Company_Revenues              Score                    Context_AVG_Employees                         Context_AVG_Revenues

            ABC Inc.                                  <100                                                            < 50 M USD                               9                         AVG of SCORE for all records in             AVG of Score for all records in                                                                                                                                                                                                                                                                                                                                 the DB where employees <100              the DB where revenue < 50 M USD                                                                                                                                                                                                                                                                                                                                 -> SQL statement                                              -> SQL statement

 

In the DataPage I have fields with SQL statements defined as Calculated Value to capture the results:                                                                                                             

Context_AVG_Employees: IsNull((SELECT AVG(Score) FROM Assessment WHERE Company_Size_Employees = [@field:Company_Size_Employees]),0)                 ->  Result is 12 = CORRECT

Context_AVG_Revenues:  IsNull((SELECT AVG(Score) FROM Assessment WHERE Company_Revenues = [@field:Company_Revenues]),0)                      ->  Result is 12 = NOT CORRECT,  should be 6                                                                                                                                                                                                                                                                                                                                                                                         

It seems that the second field takes also the value of the first calculation.

What could be the problem and what is the solution ?

Thanks !

 

 

[Nuke354]

Hello @Johan

Is this a Report DataPage? Perhaps you can try this query and add 'target.' prefix to your record fields.

Context_AVG_Employees: IsNull((SELECT AVG(Score) FROM Assessment WHERE Company_Size_Employees = target.[@field:Company_Size_Employees]),0) 

Context_AVG_Revenues:  IsNull((SELECT AVG(Score) FROM Assessment WHERE Company_Revenues = target.[@field:Company_Revenues]),0)  

We usually add 'target.' to make sure we are referencing the current row.

You may refer to this article: https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

[Johan]

Thanks Nuke354, it works now !

I also ommitted the IsNull function because the fileds are required fields.

Kind regards

Johan

[Barbie]

Hi, 

You may also refer to this article for more detailed information about the SQL functions: https://howto.caspio.com/function-reference/

- Barbie

[Johan]

Thanks Barbie

I was wonder if database related SQL functions can be used such as INSERT INTO or UPDATE ...

ALso, where can those database functions be used in Caspio ?

Johan

[CoopperBackpack]

Hello @Johan,

You may use statements to retrieve data and different functions on the DataPage level. Supported functions can be found in this article  https://howto.caspio.com/function-reference/

However, the Insert/Update/Delete actions are available in Tasks and Triggered Actions.

Please refer to these articles:
https://howto.caspio.com/tasks/
https://howto.caspio.com/tables-and-views/triggered-actions/

[Johan]

Thanks CoopperBackpack for clarification !