Aggregates & Aliases

• Aggregate functions
• Alias support (AS)

Aggregate functions

FOQL supports the aggregate functions sum, avg, min, max, and count, applied to a field inside the select list.

Function	Description
sum(field)	Sum of the field's values across the group.
avg(field)	Average of the field's values.
min(field)	Minimum value.
max(field)	Maximum value.
count(field)	Number of records satisfying the criteria.

Rules:

• Aggregates are supported on numeric field types (number, decimal, currency). count may additionally be used on lookup and enum fields.
• When the select list mixes an aggregate with scalar fields, a group by clause listing every scalar field is mandatory.

select category, status, sum(totalCost), count(id)from workorderwhere priority = 'Low'group by category, statusorder by sum(totalCost) desc

Alias support (AS)

Use as <alias> to rename a select column in the response. The as keyword must be followed by an alias identifier.

select sum(totalCost) as totalSpend,       multi_enum_field_workorder as enumLabels,       vendor.name as vendorNamefrom workordergroup by vendor.nameorder by totalSpend desc

An alias is required after as

An alias name is required after as; an empty alias is a syntax error.

Aliases may also be referenced in the order by clause (as shown with totalSpend above).