**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:

DFunction(*fieldname, table (domain), [criteria] *)

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.

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:

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:

SELECT branchsales.branch, Sum(branchsales.sales) AS SumOfsales, DSum("[sales]","[branchsales]") AS [Total Sales]
FROM branchsales
GROUP BY branchsales.branch;

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

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

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:

DFunction(*fieldname, table (domain), [criteria] *)

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**:

SELECT SUM(Balance_due) FROM Invoices

The equivalent

**DSUM** statement is:

DSUM("Balance_due", "Invoices")

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

TotalDue.text = DSUM("Balance_due", "Invoices")

This is an example of using the

**WHERE** clause of a SQL statement:

SELECT Sum(Balance_due) FROM Invoices WHERE [CustomerID] = '32413'

The equivalent

**DSUM** statement is:

DSUM("Balance_due", "Invoices", "[CustomerID] = '32413'")

### DAVG

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

DAVG("sales", "branchsales",)

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

AvgerageSales.text = DAVG("sales", "branchsales")

The SQL statement looks like this:

SELECT branchsales.branch, Avg(branchsales.sales) AS AvgSales, DAvg("[sales]","[branchsales]") AS [Average Sales]
FROM branchsales
GROUP BY branchsales.branch;

### DCOUNT

**DCOUNT** returns the number of records in the domain.

DCOUNT("Reps", "branchsales",)

### DMIN and DMAX

These return the minimum or maximum values in the domain.

DMAX("sales", "branchsales",)

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

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