The `$apply` system query option asks for totals per group instead of for rows. Available from release 27.0. It is the largest saving the protocol offers. A table of a million rows with a hundred groups answers a hundred rows, and the rows themselves never cross the network. ## One Group Column and a Count ```text GET .../odata4/ExactOnlineREST.Financial.GLAccountsBulk@eol?$apply=groupby((DivisionName),aggregate($count as Accounts)) ``` The answer holds one row per value of `DivisionName`: ```json { "@odata.context": ".../odata4/$metadata#ExactOnlineREST.Financial.GLAccountsBulk@eol(DivisionName,Accounts)", "value": [ { "DivisionName": "ACME BV", "Accounts": 642 }, { "DivisionName": "ACME Holding BV", "Accounts": 517 } ] } ``` The context names the two columns the grouping projected: the column grouped by and the name chosen for the count. That is how a client establishes that it received a grouped answer, since a service which ignored the grouping would answer ordinary rows and name the entity set alone. ## Several Group Columns and Several Totals The columns to group by sit between double brackets, and the totals follow them: ```text GET .../odata4/ExactOnlineREST.SalesInvoice.SalesInvoiceLines@eol?$apply=groupby((Division,ItemCode),aggregate($count as Lines,AmountDC with sum as Amount,AmountDC with min as Smallest,AmountDC with max as Largest,AmountDC with average as Average)) ``` Six totals are read: `$count` for the number of rows, and `min`, `max`, `sum`, `average` and `countdistinct` over one column. Each carries the name it gets in the answer, so a report can address it. ## A Condition Before the Grouping A condition belongs inside the `$apply`, in front of the grouping, separated by a slash: ```text GET .../odata4/ExactOnlineREST.SalesInvoice.SalesInvoiceLines@eol?$apply=filter(Division eq 920474)/groupby((ItemCode),aggregate(AmountDC with sum as Amount)) ``` A `$filter` next to a `$apply` is refused. It would be evaluated over the grouped answer rather than over the rows, which is a different question, and the totals it would then filter on cannot be pushed to the platform at all. ## The Distinct Combinations A grouping without totals answers the combinations that occur: ```text GET .../odata4/ExactOnlineREST.Financial.GLAccountsBulk@eol?$apply=groupby((Division,DivisionName)) ``` ## Totals Over the Whole Table A total without a grouping answers one row: ```text GET .../odata4/ExactOnlineREST.SalesInvoice.SalesInvoiceLines@eol?$apply=aggregate($count as Lines,AmountDC with sum as Amount) ``` ## The Number of Groups `$count=true` beside a grouping answers the number of groups, which tells a client how large the grouped answer will be before it asks for it: ```text GET .../odata4/ExactOnlineREST.Financial.GLAccountsBulk@eol?$apply=groupby((DivisionName),aggregate($count as Accounts))&$count=true ``` ## Case and Trailing Spaces Grouping distinguishes upper case from lower case, and it distinguishes a trailing space. `ACME BV` and `acme bv` are therefore two groups. A grouped answer holds exactly the groups the same query written by hand would produce, which is what makes the totals safe to add up in a report. ## What Is Refused A grouping which cannot be answered exactly is refused rather than approximated, because a wrong total is indistinguishable from a right one. Refused are a total over a calculation instead of over a column, a grouping over a navigation path, one name used twice in the same answer, more than one grouping, a total over the groups, and the transformations `compute`, `expand`, `concat`, `topcount`, `topsum` and `toppercent`. Beside a `$apply` the options `$filter`, `$orderby` and `$select` are refused as well; `$top` and `$count` are read. The complete list is in [[Supported OData 4 Features]]. ## Sorting a Grouped Answer A grouped answer is small by construction, so sorting it is left to the client. `$orderby` beside a `$apply` is refused, and Microsoft Power BI, Microsoft Excel and every other reporting tool sort a hundred rows without noticing.