Edit

Share via

Aggregate data using OData

  • Article

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.

Examples

Following are some examples:

These samples don't show the complete request and response for brevity.

List of unique statuses in the query

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
        }
    ]
}

Count by status values

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
        }
    ]
}

Aggregate sum of revenue

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
        }
    ]
}

Average revenue based on status

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
        }
    ]
}

Sum of revenue based on status

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
        }
    ]
}

Total account revenue by primary contact name

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>
      
    ]
}

Primary contact names for accounts in 'WA'

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)"
        }
    ]
}

Last created record date and time

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"
        }
    ]
}

First created record date and time

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"
        }
    ]
}

Distinct column values

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
      }
   ]
}

OData aggregation limitations

This section describes capabilities that are available using aggregation with FetchXml that are not currently available using OData.

Get distinct number with CountColumn

You can't get a distinct number of values using CountColumn with OData. Learn about distinct column values using FetchXml

Time zone when grouping by date

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

Row aggregate

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

Per query limit

There is no way to specify a configurable aggregate limit. Learn about per query limits using FetchXml

Limitations

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.

Next steps

Learn how to count rows.

Count rows