| AVAILABLE API VERSION |
|---|
| API v58.0 and later |
Use aggregate functions to analyze your query results. Aggregate functions include avg, count, countDistinct, min, max, and sum.
Get Average Opportunity Amount
Return the average amount on all opportunities.
You can use aggregate functions without a groupBy argument, such as using totalCount to find the total number of query results or using the avg function to find the average amount for all opportunities.
query AvgOpportunityExample {
uiapi {
aggregate {
Opportunity {
edges {
node {
aggregate {
Amount {
avg {
value
displayValue
}
}
}
}
}
totalCount
}
}
}
}
The previous query is similar to the following SOQL statement.
SELECT AVG(Amount)
FROM Opportunity
Get Average Amount of Opportunities By Campaign
Using the groupBy argument with aggregation functions enables you to perform further analysis on your results, like finding the average amount of all opportunities by campaign.
query AvgAmountPerCampaign {
uiapi {
aggregate {
Opportunity (groupBy: { CampaignId: { group: true } } ) {
edges {
node {
aggregate {
CampaignId { value }
Amount { avg { value } }
}
}
}
}
}
}
}
The previous query is similar to the following SOQL statement.
SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
Get Total Count of Records
Return the total number of account records.
query AccountsCount {
uiapi {
aggregate {
Account {
edges {
node {
aggregate {
Id { count { value } }
}
}
}
}
}
}
}
Example Response for Total Count of Records
The previous query returns this response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Id": {
"count": {
"value": 29
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT COUNT(Id)
FROM Account
Get Total Count of Records with Grouping
To use the count function and group by a field, use Id { count { value } } on the aggregate field.
query AccountsCountGroupBy {
uiapi {
aggregate {
Account ( groupBy: { Industry: { group: true } } ) {
edges {
node {
aggregate {
Industry { value }
Id { count { value } }
}
}
}
}
}
}
}
Example Response for Total Count of Records with Grouping
The previous query returns this response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Industry": {
"value": null
},
"Id": {
"count": {
"value": 18 //number of accounts without an Industry specified
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Apparel"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Biotechnology"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Construction"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Consulting"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Education"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Electronics"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Energy"
},
"Id": {
"count": {
"value": 3
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Hospitality"
},
"Id": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Transportation"
},
"Id": {
"count": {
"value": 1
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry
Get Accounts and Average Revenue of All Accounts
Return all accounts and their average annual revenue.
You can't simultaneously query both the record and aggregate fields within RecordAggregate. Query the records and aggregation separately like this.
query accounts {
uiapi {
query {
Account {
edges {
node {
Name {
value
}
}
}
}
}
aggregate {
Account {
edges {
node {
aggregate {
AnnualRevenue {
avg {
displayValue
}
}
}
}
}
}
}
}
}
Get Average Annual Revenue of Accounts
Return the average annual revenue of account records.
query AvgRevenueAccountsExample {
uiapi {
aggregate {
Account {
edges {
node {
aggregate {
AnnualRevenue {
avg {
value
displayValue
}
}
}
}
}
totalCount
}
}
}
}
Example Response for Average Annual Revenue
The previous query returns this response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"AnnualRevenue": {
"avg": {
"value": 1071125000,
"displayValue": "$1,071,125,000"
}
}
}
}
}
],
"totalCount": 24
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT AVG(AnnualRevenue), FORMAT(AVG(AnnualRevenue))
From Account
Get Distinct Number of Industries
Return the number of industries. To return the number of distinct non-null field values matching the query criteria, use the countDistinct function.
query accounts {
uiapi {
aggregate {
Account {
edges {
node {
aggregate {
Industry {
countDistinct{
value
}
}
}
}
}
totalCount
}
}
}
}
Example Response for Distinct Count
The previous query returns this response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Industry": {
"countDistinct": {
"value": 9
}
}
}
}
}
],
"totalCount": 24
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT COUNT_DISTINCT(Industry)
From Account
Get Maximum Value of a Field
To return the maximum value of a field, use the max function.
query MaxFieldGroupBy {
uiapi {
aggregate {
Campaign (groupBy: { Name: { group: true } } ) {
edges {
node {
aggregate {
Name { value }
BudgetedCost { max { value } }
}
}
}
}
}
}
}
Example Response for Maximum Value of a Field
{
"data": {
"uiapi": {
"aggregate": {
"Campaign": {
"edges": [
{
"node": {
"aggregate": {
"Name": {
"value": "DM VIP Campaign"
},
"BudgetedCost": {
"max": {
"value": 25000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "GC Product Webinar"
},
"BudgetedCost": {
"max": {
"value": 10000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "International Engineers Trade Show"
},
"BudgetedCost": {
"max": {
"value": 50000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "User Conference"
},
"BudgetedCost": {
"max": {
"value": 100000
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name
Get Sum of Open Opportunities with More Than 60% Probability
To return the sum of a numeric field, use the sum function. This example returns the opportunity amount sum grouped by name.
query SumAmountGroupBy {
uiapi {
aggregate {
Opportunity ( where: {
and: [{ IsClosed: { eq: false }},
{ Probability: { gt: 60 }
}]
},
groupBy: { Name: { group: true } } ) {
edges {
node {
aggregate {
Name { value }
Amount { sum { value } }
}
}
}
}
}
}
}
Example Response of Open Opportunities Sum
{
"data": {
"uiapi": {
"aggregate": {
"Opportunity": {
"edges": [
{
"node": {
"aggregate": {
"Name": {
"value": "Express Logistics SLA"
},
"Amount": {
"sum": {
"value": 120000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "United Oil Installations"
},
"Amount": {
"sum": {
"value": 270000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "United Oil Office Portable Generators"
},
"Amount": {
"sum": {
"value": 125000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "United Oil Refinery Generators"
},
"Amount": {
"sum": {
"value": 270000
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"value": "University of AZ Installations"
},
"Amount": {
"sum": {
"value": 100000
}
}
}
}
}
],
"totalCount": 5
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT SUM(Amount), Name
FROM Opportunity
WHERE IsClosed = false AND Probability > 60
GROUP BY Name