Aggregate functions perform certain operations over the field values and typically return a single meaningful value.

You can use aggregate functions as part of both OUTER SELECT and INNER SELECT statements. If you are not authorized to unmask the DDM-configured fields, the OUTER SELECT statement uses the masked values of these fields to process the aggregate function and returns a masked result after the processing concludes. If you have DDM privileges, the OUTER SELECT returns the unmasked result.

The INNER SELECT statement always uses the unmasked values of the DDM-configured fields for internal processing, regardless of your DDM privileges.

Examples

The following example illustrates the aggregate function using a DDM-configured field.
SELECT MAX(CreditLimit), MIN(CreditLimit), AVG(CreditLimit) 
FROM pub.Customer;
In this example, CreditLimit is a DDM-configured field. The SQL engine performs the aggregate operations using the masked value of CreditLimit for unauthorized users and the unmasked value for the authorized users.

The following example illustrates the aggregate function in the WHERE condition, utilizing a DDM-configured field.
SELECT CustNum, NAME, Phone, CreditLimit FROM pub.Customer
WHERE CreditLimit=(SELECT MIN(CreditLimit) FROM Customer);
In this example, CreditLimit is a DDM-configured field. The SQL engine processes MIN(CreditLimit) in the INNER SELECT query using the unmasked values of CreditLimit, regardless of your DDM privileges. However, the SELECT list of the query returns the masked value of CreditLimit for unauthorized users and the unmasked value for authorized users.