Specifying Aggregates
Go Up to Using Maintained Aggregates
To specify that you want to calculate summaries over the records in a client dataset, use the Aggregates property. Aggregates is a collection of aggregate specifications (Datasnap.DBClient.TAggregate). You can add aggregate specifications to your client dataset using the Collection Editor at design time, or using the Add method of Aggregates at run time. If you want to create field components for the aggregates, create persistent fields for the aggregated values in the Fields Editor.
Note: When you create aggregated fields, the appropriate aggregate objects are added to the client dataset's Aggregates property automatically. Do not add them explicitly when creating aggregated persistent fields.
For each aggregate, the Expression property indicates the summary calculation it represents. Expression can contain a simple summary expression such as
Sum(Field1)
or a complex expression that combines information from several fields, such as
Sum(Qty * Price) - Sum(AmountPaid)
Aggregate expressions include one or more of the summary operators in the following table
Summary operators for maintained aggregates :
| Operator | Use |
|---|---|
|
Sum |
Totals the values for a numeric field or expression |
|
Avg |
Computes the average value for a numeric or date-time field or expression |
|
Count |
Specifies the number of non-blank values for a field or expression |
|
Min |
Indicates the minimum value for a string, numeric, or date-time field or expression |
|
Max |
Indicates the maximum value for a string, numeric, or date-time field or expression |
The summary operators act on field values or on expressions built from field values using the same operators you use to create filters. (You can't nest summary operators, however.) You can create expressions by using operators on summarized values with other summarized values, or on summarized values and constants. However, you can't combine summarized values with field values, because such expressions are ambiguous (there is no indication of which record should supply the field value.) These rules are illustrated in the following expressions:
Sum(Qty * Price) |
{legal -- summary of an expression on fields }
|
Max(Field1) - Max(Field2) |
{legal -- expression on summaries }
|
Avg(DiscountRate) * 100 |
{legal -- expression of summary and constant }
|
Min(Sum(Field1)) |
{illegal -- nested summaries }
|
Count(Field1) - Field2 |
{illegal -- expression of summary and field }
|