The sum of operator sum the FIELDNAME across all records in a related table match the specified selection criteria. The result can appear as a list item in the detail area of a report or as a statistic in the summary area at the end of a report.
There's an important difference between the conditional statistical operator sum and the relational statistical operator sum of. sum sums up the FIELDNAME of records that satisfy a specified condition among the records being processed. sum of sums the FIELDNAME queried of matching records related to
the records being processed by the script.
TABLENAME
You can use Count of directly on a table without a predefined Relationship. Just remember that if there is a Relationship defined that doesn't have an alternative relationship name, this relationship will be named the same and the table and be used.
RELATIONSHIP
NAMED "Unique Relationship Name"If you use a relationship (alternative name) then the count of will be automatically restricted by the relational restriction. Read under TABLENAME for functionality when using Relationships without alternative relational name.
You can define an ad hoc named relationship directly in the Count Of function. If you do this you will possibly achieve two things. 1) You will insure against DataEase using a predefined relationship with the same name as the table. 2) You can reuse it again in the same script i.e. on a Sum Of etc.WITH {selection criteria}
With the WITH statement you define the relational restriction of the function. Ex. MyCustomerNR=CustomerNR and MyDate>current date.FIELDNAME
Name of the Data Column/Field that is being summed up ex. InvoiceLineSum, NumberComplaints, InvoiceTotal
Example 1
In a field derivation.Simplest type. We have two tables. ThisTable and MyCustomers. We have no relationships defined.
sum of MyCustomers TotalInvoiced
This will return the sum of what I have invoiced all my customers.
Example 2
We have two tables.CustomerType and MyCustomers. We have a relationship between them that connect CustomerType and MyCustomers on customer type.There is no Unique Alternative Name for the Relationship. I have 300 records in MyCustomer and 45 of type Good Credit. As they all buy a fixed service product that cost $10 i will have invoiced them $450.
My active record in CustomerType is the Good Credit record.
sum of MyCustomers TotalInvoiced
This will return the number $450 as I am now using the relationship instead of the table.
sum of MyCustomers named "AllRecords"
This will return the number $3000 as I have now defined a unique relationship with no restriction and I will get the total invoiced across the table..
Example 3
sum of MyCustomers named "NewRel" with RegistrationDate=current date TotalInvoiced
This will return the total invoiced today
Example 4
for MEMBERS ;
list records
LAST NAME in order ;
sum of RESERVATIONS TOTAL DUE .
end
This script tells DataEase: (1) Process all the MEMBERS records and list each member's LAST NAME in alphabetical order, (2) for each MEMBERS record processed, find all the related records in the RESERVATIONS table (those that have the same MEMBERID), and (3) list the sum of the TOTAL DUE field for the set of RESERVATIONS records that match the current MEMBERS record.
The output from this script might look as follows:
Last Name
Sum of Reservations
Total Due
Adams
$3000.00
Albert
$4760.00
Anders
$4420.00
Andersen
$2100.00
Anderson
$4320.00
Archer...
$4796.00...
If you want to include the sum of this whole group of reservations, change the fourth line of the query to read:
sum of RESERVATIONS TOTAL DUE : item sum .
Note: There's an important difference between the statistical operator sum and the relational statistical operator sum of. sum returns the total of the values in the specified field among the records being processed. sum of returns the total of the values in the specified field among the records related to the records being processed.
Relational Statistical Operator
The sum of operator adds the values in a specified field in all matching records in a related table. The result can appear as a list item in the detail area of a report or as a statistic in the summary area at the end of each group or at the end of the report.
Syntax
sum of TABLENAMERELATIONSHIP
[named "UNIQUE RELATIONSHIP NAME" ]
[with ( selection criteria) ] FIELDNAME ;  .
Returns
A numeric value.
Example
sum of RESERVATIONS TOTAL DUE .
This script tells DataEase: (1) Process all the MEMBERS records and list each member's LAST NAME in alphabetical order, (2) for each MEMBERS record processed, find all the related records in the RESERVATIONS table (those that have the same MEMBERID), and (3) list the sum of the TOTAL DUE field for the set of RESERVATIONS records that match the current MEMBERS record.
The output from this script might look as follows:
Last Name 
Sum of Reservations 

Total Due 
Adams 
$3000.00 
Albert 
$4760.00 
Anders 
$4420.00 
Andersen 
$2100.00 
Anderson 
$4320.00 
Archer... 
$4796.00... 
If you want to include the sum of this whole group of reservations, change the fourth line of the query to read:
sum of RESERVATIONS TOTAL DUE : item sum .
Note: There's an important difference between the statistical operator sum and the relational statistical operator sum of. sum returns the total of the values in the specified field among the records being processed. sum of returns the total of the values in the specified field among the records related to the records being processed.
Product: Dataease [{8}]FIVE. Written by Paul Crowther 31/07/13 at 15:06:44
Product: Dataease [{8}]FIVE. Written by DataEase Tech Sup 31/07/13 at 16:06:46
Product: Dataease [{8}]FIVE. Written by 07/12/13 at 11:45:56
Product: Dataease [{8}]FIVE. Written by DataEase 07/12/13 at 13:11:59
Product: Dataease [{8}]FIVE. Written by George Washington 17/01/17 at 08:31:26