Calculating Query Totals

The clause TOTALS ... BY allows you to define which totals must be calculated in the query. In calculating totals, aggregate functions are calculated by selection with similar field values - check points. Totals are added to the query result as totals rows.

The procedure of calculating query totals is described by the following rules:

<Totals description>
	|
	<Totals> [<Totals description>]
		|
		TOTALS  [<List of totals fields>]  BY  [OVERALL]  <List of check points>
			|			|
			<Totals field> [,<List of totals fields> [, …]]			|
				|		|
				<Aggregate_function> | <Expression> [[AS] <Field alias>]		<Check point> [, <Check point> [, …]]
							|
							<Expression> [[ONLY] HIERARCHY] | [PERIODS(SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR | TENDAYS | HALFYEAR [,<DATE type literal> | <Parameter ID>] [,<DATE type literal> | <Parameter ID>])] [[AS] Field alias]

The totals description begins with the required TOTALS keyword.

The OVERALL keyword means that an overall total row must be generated for all the query results.

Besides overall totals, you can calculate totals by check points. For this you must indicate <List of check points> after the required BY keyword. Each < Check point> contains an <Expression>, calculated when executing the query. Total rows will be calculated and added to the query result for each combination of values from these expressions.

If a check point is a reference to a catalog, you can calculate totals by catalog hierarchy. For this, you must use the required HIERARCHY keyword after the reference.

If a check point is a field - extra dimensions of accounting register, then NULL values will be ignored when receiving totals for this check point.

You can use logical expressions in this clause.

Note. The current software version does not support calculating totals by nested tables.