Aggregate data using OData
Use the $apply
option to aggregate and group your data.
The aggregate functions are limited to a collection of 50,000 records. Further information around using aggregate functionality with Dataverse can be found here: Aggregate data using FetchXml.
You can find more information about OData data aggregation here: OData extension for data aggregation version 4.0. Dataverse supports only a subset of these aggregate methods.
Note
groupby
with datetime values is not supported.$orderby
with aggregate values is not supported. This will return the error:The query node SingleValueOpenPropertyAccess is not supported
.
Following are some examples:
- List of unique statuses in the query
- Count by status values
- Aggregate sum of revenue
- Average revenue based on status
- Sum of revenue based on status
- Total account revenue by primary contact name
- Primary contact names for accounts in 'WA'
- Last created record date and time
- First created record date and time
These samples don't show the complete request and response for brevity.
GET accounts?$apply=groupby((statuscode))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2
}
]
}
GET accounts?$apply=groupby((statuscode),aggregate($count as count))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1,
"count@OData.Community.Display.V1.FormattedValue": "8",
"count": 8
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2,
"count@OData.Community.Display.V1.FormattedValue": "1",
"count": 1
}
]
}
GET accounts?$apply=aggregate(revenue with sum as total)
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"total@OData.Community.Display.V1.FormattedValue": "$440,000.00",
"total": 440000.000000000
}
]
}
GET accounts?$apply=groupby((statuscode),aggregate(revenue with average as averagevalue))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1,
"averagevalue@OData.Community.Display.V1.FormattedValue": "$53,750.00",
"averagevalue": 53750.000000000
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2,
"averagevalue@OData.Community.Display.V1.FormattedValue": "$10,000.00",
"averagevalue": 10000.000000000
}
]
}
GET accounts?$apply=groupby((statuscode),aggregate(revenue with sum as total))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1,
"total@OData.Community.Display.V1.FormattedValue": "$430,000.00",
"total": 430000.000000000
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2,
"total@OData.Community.Display.V1.FormattedValue": "$10,000.00",
"total": 10000.000000000
}
]
}
GET accounts?$apply=groupby((primarycontactid/fullname),aggregate(revenue with sum as total))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"total@OData.Community.Display.V1.FormattedValue": "$10,000.00",
"total": 10000.000000000,
"contact_fullname": "Jim Glynn (sample)"
},
{
"total@OData.Community.Display.V1.FormattedValue": "$80,000.00",
"total": 80000.000000000,
"contact_fullname": "Maria Campbell (sample)"
},
... <truncated for brevity>
]
}
GET accounts?$apply=filter(address1_stateorprovince eq 'WA')/groupby((primarycontactid/fullname))
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"contact_fullname": "Rene Valdes (sample)"
},
{
"contact_fullname": "Robert Lyon (sample)"
},
{
"contact_fullname": "Scott Konersmann (sample)"
}
]
}
GET accounts?$apply=aggregate(createdon with max as lastCreate)
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"lastCreate@OData.Community.Display.V1.FormattedValue": "3/25/2023 10:42 AM",
"lastCreate": "2023-03-25T17:42:47Z"
}
]
}
GET accounts?$apply=aggregate(createdon with min as firstCreate)
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"firstCreate@OData.Community.Display.V1.FormattedValue": "3/25/2023 10:42 AM",
"firstCreate": "2023-03-25T17:42:46Z"
}
]
}
OData doesn't have a $distinct
query option to restrict results to unique values. Instead, use the $apply
system query option with the groupby
transformation. This returns distinct values for each property.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$apply=groupby((statecode,statuscode,accountcategorycode))
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode": 1,
"statecode": 0
},
{
"statuscode": 1,
"statecode": 0,
"accountcategorycode": 1
},
{
"statuscode": 1,
"statecode": 0,
"accountcategorycode": 2
},
{
"statuscode": 2,
"statecode": 1
}
]
}
This section describes capabilities that are available using aggregation with FetchXml that are not currently available using OData.
You can't get a distinct number of values using CountColumn with OData. Learn about distinct column values using FetchXml
Grouping by parts of a date always uses UTC time and there is no way to specify that the user's time zone should be used instead available in FetchXml
When a table has a hierarchical relationship defined, you can't return a row aggregate on the lookup column for the hierarchical relationship. Learn about row aggregates using FetchXml
There is no way to specify a configurable aggregate limit. Learn about per query limits using FetchXml
Queries that return aggregate values are limited to 50,000 records. This limit helps maintain system performance and reliability. If the filter criteria in your query returns more than 50,000 records, you get the following error:
Number:
-2147164125
Code:8004E023
Message:AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Client error message: The maximum record limit is exceeded. Reduce the number of records.
To avoid this error, add appropriate filters to your query to make sure it doesn't evaluate more than 50,000 records. Then run your query multiple times and combine the results. Appropriate filters depend on the nature of your data, but they could be a date range or a subset of values in a choice column.
Learn how to count rows.