Post
Ask Question

Group By Using Rest API

The Rest-API does not support groupby functionality but we can achieve this by using CAML query in Rest-API call.

To achieve above functionality we will create one custom list called "Employee".
  1. Add two column i.e Name(SingleLine of Text), Department(Choice Field).
  2. Department choice column can have value i.e. IT,HR,Ops,Others
 Your Employee list like this.

 
Create HTML file to show the value on page using groupby on Department column. 
  1. <head>  
  2.     <script type="text/javascript" src="http://code.jquery.com/jquery-3.3.1.min.js">  
  3.     </script>  
  4.     <script type="text/javascript" src="http://win-9uqiqjpidc3:9999/sites/test/Shared%20Documents/js/CAMLCAll.js"></script>  
  5. </head>  
  6. <div id="camlQueryBind"></div>  
Rest Call using CAML Query
  1. $(document).ready(function () {  
  2.     console.log("In the ready!");  
  3.     // groupBy();  
  4.     groupBy1();  
  5. });  
  6. function groupBy() {  
  7.     // Declare the caml Query  
  8.     var viewXml = {  
  9.         ViewXml: "<View>" +  
  10.             "<Query>" +  
  11.             "<GroupBy Collapse =\"TRUE\" GroupLimit =\"100\">" +  
  12.             "<FieldRef Name=\"Department\">" +  
  13.             "</FieldRef>" +  
  14.             "</GroupBy>" +  
  15.             "</Query>" +  
  16.             "</View>"  
  17.     }  
  18.   
  19.     /// get the site url  
  20.     var siteUrl = _spPageContextInfo.siteAbsoluteUrl;  
  21.   
  22.     /// make an ajax call  
  23.     $.ajax({  
  24.         url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getByTitle('Employee')/GetItems(query=@v1)?" +  
  25.         "@v1=" + JSON.stringify(viewXml),  
  26.         type: "POST",  
  27.         dataType: "json",  
  28.         headers: {  
  29.             Accept: "application/json;odata=verbose",  
  30.             "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()  
  31.         },  
  32.         success: function (data) {  
  33.             ///do your code  
  34.             console.log(data.d.results);  
  35.             renderHTML(data);  
  36.         },  
  37.         error: function (data) {  
  38.             ///do your code  
  39.             console.log("Error======>" + data);  
  40.         }  
  41.     });  
  42. }  
  1. function renderHTML(data) {  
  2.     var camlhtml = "<table><tr><td>ID</td><td>Name</td><td>Department</td></tr>"  
  3.     $.each(data.d.results, function (index, value) {  
  4.         camlhtml += "<tr><td>" + value.ID + "</td><td>" + value.Name + "</td><td>" + value.Department + "</td></tr>"  
  5.     });  
  6.     camlhtml += "</table>";  
  7.     $('#camlQueryBind').html(camlhtml);  
  8. }  
The above performs the following actions.

function groupby() is used to call the caml query using rest api
  • Note in query we used GetItems instead of items as it's mandatory while calling caml query in rest api.
  • It should be POST request.
  • The caml query should be encoded in between this tag <View><Query>....</Query></View>
function renderHTML(data) is used to display the data by performing groupby on department.
  • $.each is used to iterate the result.
  • .html is used to bind the result with id #camlQueryBind
The result should look like this after completing the above steps.