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".

Your Employee list like this.

Create HTML file to show the value on page using groupby on Department column.

<head>
<script type="text/javascript" src="http://code.jquery.com/jquery-3.3.1.min.js">
</script>
<script type="text/javascript" src="http://win-9uqiqjpidc3:9999/sites/test/Shared%20Documents/js/CAMLCAll.js"></script>
</head>
<div id="camlQueryBind"></div>

Rest Call using CAML Query

$(document).ready(function () {
console.log("In the ready!");
// groupBy();
groupBy1();
});
function groupBy() {
// Declare the caml Query
var viewXml = {
ViewXml: "<View>" +
"<Query>" +
"<GroupBy Collapse =\"TRUE\" GroupLimit =\"100\">" +
"<FieldRef Name=\"Department\">" +
"</FieldRef>" +
"</GroupBy>" +
"</Query>" +
"</View>"
}
/// get the site url
var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
/// make an ajax call
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getByTitle('Employee')/GetItems(query=@v1)?" +
"@v1=" + JSON.stringify(viewXml),
type: "POST",
dataType: "json",
headers: {
Accept: "application/json;odata=verbose",
"X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
},
success: function (data) {
///do your code
console.log(data.d.results);
renderHTML(data);
},
error: function (data) {
///do your code
console.log("Error======>" + data);
}
});
}

function renderHTML(data) {
var camlhtml = "<table><tr><td>ID</td><td>Name</td><td>Department</td></tr>"
$.each(data.d.results, function (index, value) {
camlhtml += "<tr><td>" + value.ID + "</td><td>" + value.Name + "</td><td>" + value.Department + "</td></tr>"
});
camlhtml += "</table>";
$('#camlQueryBind').html(camlhtml);
}

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.

The result should look like this after completing the above steps.

4 Comments

Join the conversation! Your thoughts help the community grow.

Naveen Kumar8y ago
Ha ha ha.. this is not group by.. this is order by.. you can achive this easily by $orderby =Cloumnaname in Rest api url.. this doesnt need any caml query..
veena gupta8y ago
Please Provide me solution if I want result like top 2 records of each department. what query we need to add for grouping top 2 entry ?
Naveen Kumar8y ago
Its now working as expected.. retrieving all items
Naresh Singhal8y ago
Very Nice to read.........@Arvind