Domain Aggregate Functions are like SQL aggregate queries.

They give the result for a dataset (domain).

They differ from aggregate queries because you cannot group (GROUP BY) data.

Domain Aggregate syntax is:


The domain is the dataset of records being calculated. It can be a table or a query resultset.

The fieldname and dataset names must be enclosed in quotes.

Bracket are not required, but they make the statement easier to read.

Domain Aggregate

In the above example, the SUM aggregate expression will be grouped by branch,

The DSUM aggregate will be the sum for the entire domain, which is the 'branchsales' table.

Here are the results:

Domain Query

The domain amount, 4369772.4 (the query doesn't format domain amounts), is the total of all 7 branches in the branchsales table.

The SQL statement looks like this:


Here is an example of using DSUM for two different fields in the same domain.

Domain Aggregate

Domain Aggregate

You can use the criteria statement to further narrow the domain.

Domain Aggregate


Domain Aggregate

In this example, the result is the same as the SUM amount for Chicago.

The most useful Domain Aggregate Functions are listed below:

DSUM Total of a set of records
DAVG Average of the values in a set
DCOUNT Number of records in a set of records
DFIRST First record in a set
DLAST Last record in a set
DMIN Smallest value in a set
DMAX Largest value in a set


Domain Aggregate syntax is:


The domain is the set of records being calculated. It can be a table or a query resultset.

DSUM

SUM will return the SUM of all amounts in the Balance_Due field from the table Invoices:


The equivalent DSUM statement is:



Here is an example where the total Balances are placed in a textbox on a form.



This is an example of using the WHERE clause of a SQL statement:


The equivalent DSUM statement is:



DAVG

This will return the average amount of sales in the branchsales table.



Here is the statement in code to place in a textbox.

The SQL statement looks like this:

DCOUNT



DCOUNT returns the number of records in the domain.



DMIN and DMAX



These return the minimum or maximum values in the domain.



The above statement will return the maximum sales figure from the branchsales table.

















Copyright © 2006-2016, LQSystems,Inc. All rights reserved.


   Printer Friendly Page


Home