Jump to content
  • 0
Sign in to follow this  
aam82

Aggregate Calculated Fields

Question

8 answers to this question

Recommended Posts

  • 0

more information:

 

My calculated field contains a SQL formula involving 4 fields, each with data type Number. It performs a query and simple arithmetic.

 

But when I select this Calculated Field for Aggregation, I receive the error that 

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Share this post


Link to post
Share on other sites
  • 0

Now it is more clear. Thank you for additional information , aam82 !

 

This is a bug, and as far as I know Caspio will fix it in the next release very soon! 

 

Regards, 

Aurora 

Share this post


Link to post
Share on other sites
  • 0

Hello aam82,

 

As far as I know, Aggregations are sub-queries, like SELECT sum(field) FROM table WHERE criteria that are entered on the Search form.

Calculated fields are also a sub-queries, like "SELECT field FROM table WHERE criteria that are entered on the Search and the correspond record" + "SELECT field2 FROM table WHERE criteria that are entered on the Search and the correspond record".

 

So, Aggregations of Calculated fields will be like:

SELECT sum

(

"SELECT field FROM table WHERE criteria that are entered on the Search and the correspond record" + "SELECT field2 FROM table WHERE criteria that are entered on the Search and the correspond record".

)

FROM table WHERE criteria that are entered on the Search form.

 

As far as I know, sub-queries must be enclosed in parentheses. If only standard functions are used in a Calculated fields, Aggregations work correctly. But if you use additional Selects, parentheses are not used and the whole query is incorrect.

 

But I am not sure that I understand all these queries and sub-queries correctly. Maybe, some additional security limits are used for Aggregation fields.

 

Have you written the sub-query that summarize your queries and enter it in the Formula field of an Aggregation?

Share this post


Link to post
Share on other sites
  • 0

I have a calculated field that is a sum of fields in a child table to the data source.

 

My datasource is like a category:

 

    category1

 

My calculated field is a sum of items in childtable by categoryid:

 

    item1cost | categoryid1

    item2cost | categoryid1

 

aggr: sum categoryid1

 

The Aggregation I want is a sum of these sums:

 

aggr: sum of (sum of category)

 

My datasource is the category because I must condense the report output to only categories, not the granular items.

Share this post


Link to post
Share on other sites
  • 0

I've got a similar issue as aam82.  I get the "Error in the formula" even if the formula appears valid in a calculated field. I basically tried to create an aggregation in a calculated field using SELECT and the SUM function from a child table to tabulate the sum for a particular category. This might not be the proper way to do this but I need to compare the aggregated value to another calculated field. Are there any alternatives to doing these more complex calculations involving making calculations of aggregations against other non-aggregated calculated fields?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×