Aggregate Functions

For an Eloqua, HubSpot, or Marketo data store, aggregate functions can also be a part of a Select clause. Aggregate functions return a single value from a set of rows. An aggregate can be used with a field name (for example, AVG(SALARY)) or in combination with a more complex column expression (for example, AVG(SALARY * 1.07)). The column expression can be preceded by the Distinct operator. The Distinct operator eliminates duplicate values from an aggregate expression. For example:

COUNT (DISTINCT last_name)

In this example, only distinct last name values are counted.

The following table lists valid aggregate functions.

Aggregate | Returns |

SUM | The total of the values in a numeric field expression. For example, SUM(SALARY) returns the sum of all salary field values. |

AVG | The average of the values in a numeric field expression. For example, AVG(SALARY) returns the average of all salary field values. |

COUNT | The number of values in any field expression. For example, COUNT(NAME) returns the number of name values. When using COUNT with a field name, COUNT returns the number of non-NULL field values. A special example is COUNT(*), which returns the number of rows in the set, including rows with NULL values. |

MAX | The maximum value in any field expression. For example, MAX(SALARY) returns the maximum salary field value. |

MIN | The minimum value in any field expression. For example, MIN(SALARY) returns the minimum salary field value. |

STDDEV STDDEV_SAMP STDEV | The Sample Standard Deviation of the values in a group. Null values are ignored. For example, STDDEV(SALARY) returns the standard deviation for the salary field value. |

STDDEVP STDDEV_POP STDEVP | The Population Standard Deviation of the values in a group. Null values are ignored. For example, STDDEVP(SALARY) returns the population standard deviation for the salary field value. |

VAR VAR_SAMP | The Sample Variance of the values in a group. Null values are ignored. For example, VAR(SALARY) returns the sample variance for the salary field value. |

VARP VAR_POP | The Population Variance of the values in a group. Null values are ignored. For example, VARP(SALARY) returns the population variance for the salary field value. |