| AVAILABLE API VERSION |
|---|
| API v58.0 and later |
You can use aggregate functions without a groupBy argument or use them with a groupBy argument with optional types CUBE or ROLLUP.
Get Average Amount on Opportunities by Campaign
Use the aggregate function with a groupBy argument to find the average amount of all your opportunities by campaign.
query OppsByCampaign {
uiapi {
aggregate {
Opportunity (groupBy: { CampaignId: { group: true } }) {
edges {
node {
aggregate {
Amount {
avg {
value
}
}
}
}
}
}
}
}
}
This query returns the average amount of all your opportunities by campaign.
{
"data": {
"uiapi": {
"aggregate": {
"Opportunity": {
"edges": [
{
"node": {
"aggregate": {
"Amount": {
"avg": {
"value": 185806.4516129032
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
Get Average Amount on Opportunities by Account ID and Name
You can also use the aggregate function with a groupBy argument to group by multiple fields.
query OppsByCampaign {
uiapi {
aggregate {
Opportunity(groupBy: {AccountId: {group: true}, Name: { group: true }}) {
edges {
node {
aggregate {
Amount {
avg {
value
}
}
}
}
}
}
}
}
}
This query is similar to the following SOQL statement.
SELECT AVG(Amount) Amount__a
FROM Opportunity
GROUP BY AccountId, Name
Get the Number of Accounts for Each Industry and Group By Industry
query industry {
uiapi {
aggregate {
Account ( groupBy: { Industry: { group: true } } ) {
edges {
node {
aggregate {
Industry {
value
}
Name {
count {
value
}
}
}
}
}
}
}
}
}
This query provides the following response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Industry": {
"value": null
},
"Name": {
"count": {
"value": 18
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Apparel"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Biotechnology"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Construction"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Consulting"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Education"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Electronics"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Energy"
},
"Name": {
"count": {
"value": 3
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Hospitality"
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Transportation"
},
"Name": {
"count": {
"value": 1
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT Industry, COUNT(Name)
FROM Account
GROUP BY Industry
Get Accounts and Group By Year
query GroupAccountsByYear {
uiapi {
aggregate {
Account (groupBy: { CreatedDate: { function : CALENDAR_YEAR } } ) {
edges {
node {
aggregate {
Name {
count {
value
}
}
CreatedDate {
calendarYear {
value
}
}
}
}
}
}
}
}
}
This query provides the following response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Name": {
"count": {
"value": 24
}
},
"CreatedDate": {
"calendarYear": {
"value": 2022
}
}
}
}
},
{
"node": {
"aggregate": {
"Name": {
"count": {
"value": 5
}
},
"CreatedDate": {
"calendarYear": {
"value": 2023
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT Count(Name), CALENDAR_YEAR(CreatedDate)
FROM Account
GROUP BY CALENDAR_YEAR(CreatedDate)
Get the Sum of Accounts for Combination of Type and Industry
query aggregates {
uiapi {
aggregate {
Account(groupBy: { type: CUBE, Industry: { group: true }, Type: { group: true } }) {
edges {
node {
aggregate {
Industry {
value
grouping {
value
}
}
Type {
value
grouping {
value
}
}
NumberOfEmployees {
sum {
value
}
}
}
}
}
}
}
}
}
This query provides the following response.
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Industry": {
"value": null,
"grouping": {
"value": 0
}
},
"Type": {
"value": null,
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": null
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": null,
"grouping": {
"value": 0
}
},
"Type": {
"value": null,
"grouping": {
"value": 1
}
},
"NumberOfEmployees": {
"sum": {
"value": null
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Manufacturing",
"grouping": {
"value": 0
}
},
"Type": {
"value": "Prospect",
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": 680
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Manufacturing",
"grouping": {
"value": 0
}
},
"Type": {
"value": null,
"grouping": {
"value": 1
}
},
"NumberOfEmployees": {
"sum": {
"value": 680
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Media",
"grouping": {
"value": 0
}
},
"Type": {
"value": "Prospect",
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": 14668
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Media",
"grouping": {
"value": 0
}
},
"Type": {
"value": null,
"grouping": {
"value": 1
}
},
"NumberOfEmployees": {
"sum": {
"value": 14668
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Technology",
"grouping": {
"value": 0
}
},
"Type": {
"value": "Customer",
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": null
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Technology",
"grouping": {
"value": 0
}
},
"Type": {
"value": null,
"grouping": {
"value": 1
}
},
"NumberOfEmployees": {
"sum": {
"value": null
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": null,
"grouping": {
"value": 1
}
},
"Type": {
"value": null,
"grouping": {
"value": 1
}
},
"NumberOfEmployees": {
"sum": {
"value": 15348
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": null,
"grouping": {
"value": 1
}
},
"Type": {
"value": null,
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": null
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": null,
"grouping": {
"value": 1
}
},
"Type": {
"value": "Prospect",
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": 15348
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": null,
"grouping": {
"value": 1
}
},
"Type": {
"value": "Customer",
"grouping": {
"value": 0
}
},
"NumberOfEmployees": {
"sum": {
"value": null
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to the following SOQL statement.
SELECT Industry, Type, SUM(NumberOfEmployees)
FROM Account
GROUP BY CUBE(Industry, Type)