Hybrid Data Pipeline supports a subset of the functionality defined by the OData Version 4 extension for data aggregation. Aggregation functionality is extended with the $apply query parameter. The following examples are based on the Example STORES entity.

Example STORES entity

The STORES entity has the following tabular representation.

ID NAME CATEGORY QUANTITY COST TAX IMPORTED
1 Razor Personal Care 4 3.50 0.06 true
2 Shampoo Personal Care 99 6.70 0.06 false
3 Lotion Personal Care 2 4.22 0.05 false
4 Beer Adult Beverage 88 9.99 0.09 false
5 Wine Adult Beverage 21 21.99 0.09 true

Sum of all field values

The following query requests the sum of values in the QUANTITY field.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=aggregate(QUANTITY
   with sum as Total)

Result

{
    "@odata.context": "https://<myserver>:<port>/api/odata4/sforce_odata_v4/$metadata#STORES(Total)",
    "value": [
        {
            "@odata.type": "#sforce_odata_v4.STORE",
            "@odata.id": null,
            "Total@odata.type": "#Int64",
            "Total": 214
        }
    ]
}

Sum, average, max, min, and distinct quantities

The following query requests the sum of values, the average of values, the maximum value, the minimum value, and the number of distinct values in the QUANTITY field.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=aggregate(QUANTITY
   with sum as Total,QUANTITY with average as Average,QUANTITY with max as Max,QUANTITY
   with min as Min,QUANTITY with countdistinct as CountDistinct)

Result

{
    "@odata.context": "https://<myserver>:<port>/api/odata4/sforce_odata_v4/$metadata#STORES
                       (Total,Average,Max,Min,CountDistinct)",
    "value": [
        {
            "@odata.type": "#sforce_odata_v4.STORE",
            "@odata.id": null,
            "Total@odata.type": "#Int64",
            "Total": 214,
            "Average@odata.type": "#Decimal",
            "Average": 42.8,
            "Max@odata.type": "#Int32",
            "Max": 99,
            "Min@odata.type": "#Int32",
            "Min": 2,
            "CountDistinct@odata.type": "#Int64",
            "CountDistinct": 5
        }
    ]
}

Sum of quantity values greater than or equal to 21

The following query requests the sum of values in the QUANTITY field greater than or equal to 21.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=filter(QUANTITY ge 21)/
   aggregate(QUANTITY with sum as Total)

Result

{
    "@odata.context": "https://<myserver>:<port>/api/odata4/sforce_odata_v4/$metadata#STORES(Total)",
    "value": [
        {
            "@odata.type": "#sforce_odata_v4.STORE",
            "@odata.id": null,
            "Total@odata.type": "#Int64",
            "Total": 208
        }
    ]
}

Group by category

The following query uses groupby to retrieve category information.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=groupby((CATEGORY))

Result

{
   "@odata.context": "https://<myserver>:<port>/api/odata4/PUBLIC/$metadata#STORES(CATEGORY)",
   "value": [
      {
         "CATEGORY": "Personal Care"
      },
      {
         "CATEGORY": "Adult Beverage"
      }
   ]
}

Group by category with countdistinct

The following query uses returns a count for distinct categories.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=groupby((CATEGORY),aggregate
   (CATEGORY with countdistinct as Count))

Result

{
   "@odata.context": "https://<myserver>:<port>/api/odata4/PUBLIC/$metadata#STORES(Count,CATEGORY)",
   "value": [
      {
         "Count": 3,
         "CATEGORY": "Personal Care"
      },
      {
         "Count": 2,
         "CATEGORY": "Adult Beverage"
      }
   ]
}

Group by using filter transformation and $filter query parameter

The following query uses the filter transformation to identify categories and then uses the $filter parameter to filter by the given condition.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=filter(IMPORTED ne true)/
   groupby((CATEGORY),aggregate(CATEGORY with countdistinct as Count))&$filter=Count ge 2

Result


{
    "@odata.context":"https://<myserver>:<port>/api/odata4/PUBLIC/$metadata#STORES(Count,CATEGORY)",
    "value":[
        {
            "Count":2,
            "CATEGORY":"Personal Care"
        }
    ]
}

Multiple aggregates

The following query returns multiple OData aggregates.

Query

GET https://<myserver>:<port>/api/odata4/sforce_odata_v4/STORES?$apply=aggregate(QUANTITY with sum
   as Total,$count as Count,QUANTITY with max as MAXIMUM,QUANTITY with min as MININUM,CATEGORY with
   countdistinct as NUM_CATS,QUANTITY with average as AVERAGE)

Result

{
   "@odata.context": "https://<myserver>:<port>/api/odata4/PUBLIC/$metadata#STORES(Total,Count,MAXIMUM,
                      MININUM,NUM_CATS,AVERAGE)",
   "value": [
      {
         "Total": 214,
         "Count": 5,
         "MAXIMUM": 99,
         "MININUM": 2,
         "NUM_CATS": 5,
         "AVERAGE": 42
      }
   ]
}