Edit

Share via

Aggregate data by using OData

Use the OData $apply option to aggregate and group your data in Microsoft Dataverse. This query option enables you to perform calculations like sum, count, average, and grouping operations on collections of up to 50,000 records.

The aggregate functions work with a collection of up to 50,000 records. For more information about using aggregate functionality with Dataverse, see Aggregate data using FetchXml.

For more information about OData data aggregation, see OData extension for data aggregation version 4.0. Dataverse supports only a subset of these aggregate methods.

Note

  • groupby with datetime values isn't supported.

  • $orderby with aggregate values isn't supported. This limitation returns the error: The query node SingleValueOpenPropertyAccess is not supported.

Examples

The following examples show how to use aggregate functions:

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 method 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 by using aggregation with FetchXml that aren't currently available by using OData.

Get distinct number with CountColumn

You can't get a distinct number of values by 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's no way to specify that the user's time zone should be used instead. Learn about grouping by parts of a date 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

You can't 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

  • Last updated on