skip to main content
Supported SQL and Extensions : Oracle Eloqua, HubSpot, and Marketo : Select : Select Clause : Aggregate Functions
 

Try DataDirect Cloud Now
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.
Table 45. 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.