The means of getting total data by field described in the previous subsection allow the user to receive total values by any field easily. However, using these means it is not easy to receive answers to requests like: “how many inhabitants are there in every street?” because multiple search by every street should be performed. ObjectLand provides a more convenient possibility to receive answers to such requests.
All the queries described above were built by selecting individual fields or records of base tables. The values of record fields displayed in the window fully correspond to the field values of base tables. ObjectLand permits appending additional fields to the query. The values of these fields are formed as a result of performing some calculations with field values of base tables. Such fields are called calculated. Query fields which are not calculated will be called primary fields below.
A calculated field is determined by a computing operation and it is built on the basis of some primary field. The primary field is “overridden” by a calculated one and becomes invisible. Further, the primary field on the basis of which the calculated one is built will be called overridden. Visible not overridden primary fields form a calculation group which controls building-up of calculated fields.
Thus, a calculated field is determined by the operation and overridden primary field. A set of calculated fields and calculation group determine the calculating condition.
Building up values of calculated fields is performed in the following way:
if the search condition is specified or the indication of removing duplicated records is set, a query containing records by which forming of calculated fields will occur is built;
all query records are divided into subsets with the same values of all fields which are included into the calculation group;
for every subset of records the value of calculated fields is formed by performing necessary operations with values of overridden primary fields by all subset records;
apart from forming values of calculated fields for a subset of records, the system forms total values of calculated fields by all query records. For every calculated field the total is the result of the same operation which is used when forming the present field. That is, if the calculated field is formed using the operation of summation, the total record for the present field will contain the sum of values of the primary field for all records in the query. If the operation of evaluating the maximum was used, the total field will contain the maximum value and so on;
a query containing all calculated fields and fields of calculation group is built. Every record of the created query corresponds to one subset of records. Fields of calculation group contain values determining the subset of records, calculated fields of each record contain values calculated for the subset. Total values of calculated fields form the total record. When browsing the query the total record is the last one to be displayed, the line “Total” in the pane of record numbers corresponds to it. If the calculation group is absent, the query will contain only the total record.
To answer the request formulated at the beginning of this subsection it is necessary to form the calculation condition composed of one calculated field with the operation of addition overriding the primary field “Total Inhabitants” and the calculation group containing the only field – “Street”.
Admissible computing operations for different types of primary fields are given in the table 29-3. In the first table column all possible operations are enumerated. In next columns admissibility of the operation depending on the field type is indicated (“True” in the table cell means admissibility of the corresponding operation for the present field type). Description of the operations is presented in table 29-4.
Field type | |||||
Operation | Integer | Real | String | Date, Time, Timestamp | Boolean |
Number of records | True | True | True | True | True |
Number of not empty records | True | True | True | True | True |
Number of empty records | True | True | True | True | True |
Minimum | True | True | False | True | False |
Maximum | True | True | False | True | False |
Average | True | True | False | False | False |
Sum | True | True | False | False | False |
Number of values “True” | False | False | False | False | True |
Number of values “False” | False | False | False | False | True |
Table 29-3. Operations for calculated fields
Operation | Description |
Number of records | Total number of records in a query is calculated |
Number of not empty records | Number of records in a query containing not empty value in the field is calculated |
Number of empty records | Number of records in a query containing empty value in the field is calculated |
Minimum | Minimum field value among query records is evaluated; records with empty field value are not taken into account. |
Maximum | Maximum field value among query records is evaluated; records with empty field value are not taken into account |
Average | Average field value by records in a query is calculated; records with empty field value are not taken into account |
Sum | Sum of field values in query records is calculated; records with empty field value are not taken into account |
Number of values “True” | Number of query records having the “True” field value is calculated; records with empty field value are not taken into account |
Number of values “False” | Number of query records having the “False” field value is calculated; records with empty field value are not taken into account |
Table 29-4. Description of operations for calculated fields
A list of admissible computing operations for the primary field determines the maximum number of calculated fields which can be built for one primary field.
It should be noted that if the primary field overridden by the calculated field uses a reference table, the values taken from the reference table take part in the calculation. The type of the primary field is determined by the type of the reference field value.
When working with calculated fields it is necessary to take into account the following aspects:
for tables with a big number of records forming values of calculated fields can require considerable time in case of presence of the calculation group, because the system will perform preliminary sorting of records;
if the sorting condition is not specified, after forming values of calculated fields the records will be sorted in accordance with internal sorting condition which includes all fields of calculation group for each of which the ascending order is specified. Priority of fields in internal sorting condition is determined by their relative position.
calculated fields cannot be used when specifying search condition;
calculated fields can be used when specifying sorting condition;
the command Field+Total can be performed for a calculated field. In this case total information displayed in the dialog box “Total by field” is calculated by values of the calculated field, and not by values of overridden primary field;
it is impossible to perform adding, removing and editing records for the query containing calculated fields .
 |  |
