| AVAILABLE API VERSION |
|---|
| API v59.0 and later |
You can use aggregate functions with or without an orderBy argument.
Example: Query Aggregate Fields with Ordering
This example queries the Industry field and an aggregate count function on Name, grouped by Industry. Use the orderBy argument for the aggregated Name field.
The result lists all industries and the number of accounts that are assigned to that particular industry.
By default, null values are returned first the results. To remove accounts without a specified industry, use where: { Industry: { ne: null } }.
note: All aggregate functions ignore null values, except for COUNT() and COUNT(Id).
query IndustryAndNameCount {
uiapi {
aggregate {
Account ( where: { Industry: { ne: null } },
groupBy: { Industry: { group: true } },
orderBy: { Name: { order: DESC, function: COUNT }}){
edges {
node {
aggregate {
Industry {
value
}
Name {
count { value }
}
}
}
}
}
}
}
}
Example Response for Aggregate Fields with Ordering
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Industry": {
"value": "Energy"
},
"Name": {
"count": {
"value": 3
}
}
}
}
},
{
"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
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to this SOQL statement.
SELECT Industry, COUNT(Name)
FROM Account
WHERE Industry != null
GROUP BY Industry
ORDER BY COUNT(Name) DESC
Similar to SOQL, you can't order by the aggregated name count unless you also group by Industry. For example, you get an error if you don't provide GROUP BY Industry.
You also can't group by the aggregated name count. Only date aggregate functions are allowed in a grouping.
Example: Query Multiple Aggregate Fields with Ordering
This example queries the Industry field with 2 aggregate functions on AnnualRevenue and Name fields. Use the orderBy argument for the aggregated fields.
The result lists all industries, the corresponding average amount on the AnnualRevenue fields, and the number of accounts that are assigned to that particular industry.
query IndustryAndAvgRevenue {
uiapi {
aggregate {
Account ( orderBy: {
Name: { order: ASC, function: COUNT },
AnnualRevenue: { order: ASC, function: AVG }
},
groupBy: { Industry: { group: true } } ) {
edges {
node {
aggregate {
Industry { value }
AnnualRevenue {
avg { value }
}
Name {
count { value }
}
}
}
}
}
}
}
}
Example Response for Multiple Aggregate Fields with Ordering
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"Industry": {
"value": "Education"
},
"AnnualRevenue": {
"avg": {
"value": null
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Biotechnology"
},
"AnnualRevenue": {
"avg": {
"value": 30000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Consulting"
},
"AnnualRevenue": {
"avg": {
"value": 50000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Electronics"
},
"AnnualRevenue": {
"avg": {
"value": 139000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Apparel"
},
"AnnualRevenue": {
"avg": {
"value": 350000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Hospitality"
},
"AnnualRevenue": {
"avg": {
"value": 500000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Construction"
},
"AnnualRevenue": {
"avg": {
"value": 950000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Transportation"
},
"AnnualRevenue": {
"avg": {
"value": 950000000
}
},
"Name": {
"count": {
"value": 1
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": "Energy"
},
"AnnualRevenue": {
"avg": {
"value": 5600000000
}
},
"Name": {
"count": {
"value": 3
}
}
}
}
},
{
"node": {
"aggregate": {
"Industry": {
"value": null
},
"AnnualRevenue": {
"avg": {
"value": null
}
},
"Name": {
"count": {
"value": 18
}
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to this SOQL statement.
SELECT Industry, AVG(AnnualRevenue), COUNT(Name)
FROM Account
GROUP BY Industry
ORDER BY COUNT(Name), AVG(AnnualRevenue)
Example: Query Minimum Revenue of All Industries with Ordering
To return the minimum value of a field, use the min function. This example returns the minimum value of the AnnualRevenue field for each industry.
query MinRevenueGroupByIndustry {
uiapi {
aggregate {
Account (groupBy: { Industry: { group: true } }, orderBy: { AnnualRevenue: { function: MIN } } ) {
edges {
node {
aggregate {
AnnualRevenue { min { displayValue } }
Industry { value }
}
}
}
}
}
}
}
The orderBy argument orders the results in ascending order of annual revenue. Similar to SOQL, you can't order by the aggregated minimum annual revenue unless you also group by Industry. In other words, if you use orderBy in a query, you must also include the groupBy argument.
Example Response for Minimum Revenue of All Industries
{
"data": {
"uiapi": {
"aggregate": {
"Account": {
"edges": [
{
"node": {
"aggregate": {
"AnnualRevenue": {
"min": {
"displayValue": "$30,000,000"
}
},
"Industry": {
"value": "Biotechnology"
}
}
}
},
{
"node": {
"aggregate": {
"AnnualRevenue": {
"min": {
"displayValue": "$50,000,000"
}
},
"Industry": {
"value": "Consulting"
}
}
}
},
{
"node": {
"aggregate": {
"AnnualRevenue": {
"min": {
"displayValue": "$139,000,000"
}
},
"Industry": {
"value": "Electronics"
}
}
}
},
{
"node": {
"aggregate": {
"AnnualRevenue": {
"min": {
"displayValue": "$350,000,000"
}
},
"Industry": {
"value": "Apparel"
}
}
}
},
{
"node": {
"aggregate": {
"AnnualRevenue": {
"min": {
"displayValue": "$500,000,000"
}
},
"Industry": {
"value": "Hospitality"
}
}
}
},
{
"node": {
"aggregate": {
"AnnualRevenue": {
"min": {
"displayValue": "$5,600,000,000"
}
},
"Industry": {
"value": "Energy"
}
}
}
}
]
}
}
}
},
"errors": []
}
The previous query is similar to this SOQL statement.
SELECT MIN(AnnualRevenue), Industry
FROM Account
GROUP BY Industry
ORDER BY MIN(AnnualRevenue)