Recents

 


Sum, Count, Average formulasOfficial Content

Sum, Count and Average are Aggregate Formulas.

Syntax#

{Sum | Count | Average}( <aggregateExpression>, <aggregateCondition>, <defaultValue> ) [if <triggeringCondition>]

where:

<aggregateExpression>

Is the expression to be aggregated, that is to say, the expression to be summed up, or averaged. It may contain attributes (even formula attributes), constants and variables (variables only are allowed in inline formulas).

For Sum and Average, the
<aggregateExpression> result data type must be numeric. For Count, the first parameter must be an attribute (not an expression) that belongs to the table in which you need to count records.

<aggregateCondition>

Is a combination of a search condition with a Data Selector invocation. Both parts are optional:

[<SearchCondition>] [USING <DataSelector> '(<Parameter>1, <Parameter>2, <Parameter>n ')']]

<SearchCondition>

Is the condition that records must verify to be considered in the aggregation. It may contain attributes, constants and variables (user variables are allowed only in inline formulas, GeneXus standard variables in global formulas and inline formulas). 

<defaultValue>

Is the returned value when no records match the <aggregateCondition>. It is a constant and it is optional.

<triggeringCondition>

Is the condition that determines if the formula must be triggered or not. It is optional. The only attributes allowed are those belonging to the contextual table (that the formula attribute would belong to if it were stored) and its extended.

 

Note: Return value is rounded according to the definition of the attribute or variable which is assigned with the aggregate expression.
For instance, if we assign AttributeB=Average(AttributeA) having 3 records of AttributeA with values 1, 3 and 1,

  • if AttributeB is defined as N(10.5), will have value 1,66667
  • if AttributeB is defined as N(7.2), will have value 1,67

Examples#

Transactions#

Flight
{
   FlightId*
   FlightDescription
   FlightPrice
   FlightInstanceAveragePrice = Average(FlightInstancePrice, FlightInstanceDate = Today(), 0);
   FlightInstanceTotalPrice = Sum(FlightInstancePrice);
   FlightInstanceCountDate = Count(FlightInstanceNumber) if FlightPrice > 100;
}

FlightInstance
{
   FlightInstanceNumber*
   FlightId
   FlightDescription
   FlightPrice
   FlightInstanceDate
   FlightInstanceNumberOfPassengers
   FlightInstancePrice = FlightPrice if FlightInstanceNumberOfPassengers <= 100;
	FlightPrice * 0.9  if  FlightInstanceNumberOfPassengers > 100 and FlightInstanceNumberOfPassengers < 200;
	FlightPrice * 0.8  otherwise;
   FlightInstanceNumberOfPassengers = Count(PassengerName,PassengerName = "Smith",0);
   {
      PassengerId*
      PassengerName
   }
}

The above example shows several Aggregate global formulas with their associated definitions (note that all the defined formulas are Aggregate formulas except for the FlightInstancePrice formula).



Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.