SharePoint ListItems Group By

November 21, 2016August 28, 2017 Piyush K Singh30 Comments

SharePoint Grouped ListItems

In the above screenshot, I have applied a GroupBy setting on a SharePoint View. I needed the same exact values in my application through JSOM. However, all of the online examples, that I came across, were of retrieving all the ListItems of a SharePoint List. You can either retrieve all the items at once or in batches, but there seems to be no provision of applying grouping or aggregation on the ListItems through code!

Somehow, I wasn’t convinced of this and was vehemently against this idea of pulling all the data on client-side first, thereafter applying grouping and aggregations.

As it turned out, there’s a way to directly query grouped data from SharePoint, saving the overhead of doing it all on client-side. In this post, I’ll show how to retrieve items from SharePoint, grouped by a SharePoint field using CSOM & JSOM. If you’re interested in applying aggregations (read TOTALS) only, without grouping, then kindly refer to this post, SharePoint TOTALS.

CSOM

SP = Microsoft.SharePoint.Client;

using (SP.ClientContext ctx = new SP.ClientContext(siteUrl))
{
	ctx.Credentials = new SP.SharePointOnlineCredentials("piyush@something.onmicrosoft.com", SecureStringPwd);
	SP.List list = ctx.Web.Lists.GetByTitle("GroupByTest");
	string viewXml = "<View><Query><GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"CompanyName\"/></GroupBy></Query><ViewFields><FieldRef Name=\"LinkTitle\"/><FieldRef Name=\"CompanyName\"/><FieldRef Name=\"Profit\"/></ViewFields><RowLimit Paged=\"TRUE\">30</RowLimit><Aggregations Value=\"On\"><FieldRef Name=\"Profit\" Type=\"SUM\"/></Aggregations></View>";


	SP.ClientResult<string> groupBy = list.RenderListData(viewXml);

	ctx.ExecuteQuery();
}

App/Add-In

Following is the code snippet for a SharePoint hosted app.

viewXml = {
'query':
'<View><Query><GroupBy Collapse="TRUE" GroupLimit="30"><FieldRef Name="CompanyName"/></GroupBy></Query><ViewFields><FieldRef Name="LinkTitle"/><FieldRef Name="CompanyName"/><FieldRef Name="Profit"/></ViewFields><RowLimit Paged="TRUE">30</RowLimit><Aggregations Value="On"><FieldRef Name="Profit" Type="SUM"/></Aggregations></View>'
}

jQuery.ajax({
        url: "appWebUrl" + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'" + listID + "')/renderlistdata(@viewXml)?@viewXml='" + viewXml.query + "'&$select=" + fields + "&@target='hostWebUrl'",
        type: "POST",
        headers: { 
            "Accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val()
        },
        success: doSuccess,
        error: doError
});

JSOM

var list = clientContext.get_web().get_lists().getByTitle('GroupByTest');
var viewXml = "<View><Query><GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"CompanyName\"/></GroupBy></Query><ViewFields><FieldRef Name=\"LinkTitle\"/><FieldRef Name=\"CompanyName\"/><FieldRef Name=\"Profit\"/></ViewFields><RowLimit Paged=\"TRUE\">30</RowLimit><Aggregations Value=\"On\"><FieldRef Name=\"Profit\" Type=\"SUM\"/></Aggregations></View>";
var groupBy = list.renderListData(viewXml);
clientContext.executeQueryAsync(function(){console.log(groupBy); },function(e){console.log(e);})

As you can clearly see that the trick lies in the CAML query (viewXml). We have to embed the grouping information in it to fetch grouped listitems.

CAML

<View>
	<Query>
		<GroupBy Collapse="TRUE" GroupLimit="30">
			<FieldRef Name="CompanyName"/>
		</GroupBy>
	</Query>
	<ViewFields>
		<FieldRef Name="LinkTitle"/>
		<FieldRef Name="CompanyName"/>
		<FieldRef Name="Profit"/>
	</ViewFields>
	<RowLimit Paged="TRUE">30</RowLimit>
	<Aggregations Value="On">
		<FieldRef Name="Profit" Type="SUM"/>
	</Aggregations>
</View>

The output will be in the following format:

{
	"Row": [{
		"CompanyName": "A",
		"CompanyName.urlencoded": "%3B%23A%3B%23",
		"CompanyName.COUNT.group": "3",
		"CompanyName.newgroup": "1",
		"CompanyName.groupindex": "1_",
		"Profit.SUM": "\u20b9 221.43",
		"Profit.SUM.agg": "\u20b9 157.44"
	},
	{
		"CompanyName": "B",
		"CompanyName.urlencoded": "%3B%23B%3B%23",
		"CompanyName.COUNT.group": "2",
		"CompanyName.newgroup": "1",
		"CompanyName.groupindex": "2_",
		"Profit.SUM": "\u20b9 221.43",
		"Profit.SUM.agg": "\u20b9 63.99"
	}],
	"FirstRow": 1,
	"FolderPermissions": "0x7fffffffffffffff",
	"LastRow": 2,
	"FilterLink": "?",
	"ForceNoHierarchy": "1",
	"HierarchyHasIndention": ""
}

For aggregation, SharePoint supports the following values

Si No.	Total Function	Function Type
1	Count	COUNT
2	Average	AVG
3	Maximum	MAX
4	Minimum	MIN
5	Sum	SUM
6	Std Deviation	STDEV
7	Variance	VAR

Key Takeaway

The output will be in the format, [Field].[Aggregation].agg. In this case, since I wanted the sum of the field, Profit, the output key is in the format, Profit.SUM.agg, where agg is always constant.

30 thoughts on “SharePoint ListItems Group By”

Richard says:

November 22, 2016 at 17:28

Hi When I try this I get an exception around RenderListData something about it not being a function.
What version of Microsoft.Sharepoint.Client are you using? and what version of sharepoint. I’m on 2010 – does that make a difference?

LikeLike

Reply
- Piyush says:
  
  November 22, 2016 at 18:19
  
  Hi Richard,
  
  Yes, unfortunately it makes a whole lot of difference. RenderListData seems to be introduced since, SharePoint 2013. I have used the above example on SharePoint Online. It should work on SharePoint 2013 & SharePoint 2016 as well.
  
  The version of my, ‘Microsoft.SharePoint.Client’ dll is, ‘16.0.0.0’.
  
  LikeLike
  
  Reply
Richard says:

November 23, 2016 at 14:46

Oh well – I guess it’ll be a nice ‘upgrade’ that I can do when we move to sharepoint 2013.
Still a nice article and against a lot of ‘can’t be done’ stuff I read.

Ta’s for the quick response.
Richard

LikeLike

Reply
- Piyush says:
  
  November 23, 2016 at 18:19
  
  Absolutely, most of the articles on the net suggests that it’s not possible. But somehow, I had my faith in SharePoint! It never ceases to amaze you 🙂
  
  LikeLike
  
  Reply
Query ListItems in Batches – SharePoint Online | SharePoint Blog - Piyush says:

December 4, 2016 at 10:47

[…] Here’s my blog post for applying Grouping through CSOM, SharePoint ListItems Group By. […]

LikeLike

Reply
jeff says:

August 22, 2017 at 16:19

Hi, I use jsom, but happen a error
the server responded with a status of 403 .There is no app context to execute this request

LikeLike

Reply
- jeff says:
  
  August 22, 2017 at 16:26
  
  Ok. I know ,the url is error . it should be
  “appWebUrl” + “/_api/Web/Lists(guid'” + listID + “‘)/renderlistdata(@viewXml)?@viewXml='” + viewXml.query + “‘&$select=” + fields + “&@target=’hostWebUrl'”
  
  Thank you very much!
  
  LikeLike
  
  Reply
  - Piyush says:
    
    August 22, 2017 at 17:11
    
    OK, the url used in the JSOM ex is for a SharePoint App. That’s why I was using AppContextSite. For plain JSOM, to be directly used inside SharePoint, your url is correct. Will update the post accordingly. Thanks 🙂
    
    LikeLike
jeff says:

August 23, 2017 at 11:15

Thanks for your help, but I met a new question:

to load resource: the server responded with a status of 500 (Internal Server Error)

The HTTP method ‘GET’ cannot be used to access the resource ‘RenderListData’. The operation type of the resource is specified as ‘Default’. Please use correct HTTP method to invoke the resource.

When I have three where conditions, the above error will occur
My ViewXml like this:

L89

Jar – Plastic

20

When the where condition is less than three, no error occurred, and the ViewXml like this:

L89

Jar – Plastic

I think there may be too long url, do you have any suggestions?

Thanks ☺

LikeLike

Reply
- jeff says:
  
  August 23, 2017 at 11:18
  
  Sorry, the query is formatted and is not displayed.
  three conditions
  ”
  
  L89
  
  Jar – Plastic
  
  20
  
  ”
  two conditions
  ”
  
  L89
  
  Jar – Plastic
  
  “
  
  LikeLike
  
  Reply
  - Piyush says:
    
    August 23, 2017 at 16:07
    
    Hey, can you please encode your code inside the following tag in your comment. ‘Cause otherwise it’s bit hard to figure out,
    
    In general, url characters up to 2000 characters works perfectly fine.So, can you also check the entire length of the url that is being sent?
    
    LikeLike
jeff says:

August 23, 2017 at 18:10

Hi, Piyush

Thank you very much for your reply. After debugging, I found that the problem is not url length.
The problem is that the where statement is malformed. When there are three conditions, my format is like:
(And)
where1
where2
where3
(/And)
In fact this is the wrong way to write. it should be:
(And)
where1
(And)
where2
where3
(/And)
(/And)

here
That’s all 😊

LikeLike

Reply
- jeff says:
  
  August 23, 2017 at 18:28
  
  In addition, if i query the data is using the following wording:
  var clientContext = new SP.ClientContext(siteUrl);
  clientContext.executeQueryAsync(fun,fun)
  
  How can i use the function of the RenderListData ?
  
  LikeLike
  
  Reply
  - Piyush says:
    
    August 24, 2017 at 20:36
    Hi, Jeff,
    
    Thanks to your comment, I can see the problem in the post. The ex given above is not for JSOM but for SharePoint REST API (App). The query for JSOM would be
    
    var list = clientContext.get_web().get_lists().getByTitle('listTitle'); var groupBy = list.renderListData(viewXml); clientContext.executeQueryAsync(function(){console.log(groupBy); },function(e){console.log(e);})
    
    Will update the post accordingly. Thanks! 🙂
    
    LikeLike
jeff says:

August 28, 2017 at 10:42

You are so professional in sharepoint, your blog helped me a lot. Thanks for your help. 🙂

LikeLike

Reply
- Piyush says:
  
  August 28, 2017 at 15:50
  
  Thanks! 🙂
  
  LikeLike
  
  Reply
jeff says:

September 5, 2017 at 11:39
Hi, Piyush

Have you ever encountered such a problem?
My list has more than 5000 items. When I use ‘Group by’ in CSOM, there is an exception:
Additional information: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

And my query is:
```
<View>
	<Query>
		<GroupBy Collapse='TRUE' GroupLimit='200'><FieldRef Name='SU'/></GroupBy>
	</Query>
	<ViewFields>
		<FieldRef Name='SU'/>
	</ViewFields>
</View>
```
I have referred to your article: Query ListItems in Batches – SharePoint Online
And updated my query:
```
<View>
	<Query>
		<GroupBy Collapse='TRUE' GroupLimit='200'><FieldRef Name='SU'/></GroupBy>
	</Query>
	<ViewFields>
		<FieldRef Name='SU'/>
	</ViewFields>
	<RowLimit Paged='TRUE'>30</RowLimit>
</View>
```
But will still throw the same exception. Do you have any advice?

LikeLike
Reply
- Piyush says:
  
  September 6, 2017 at 20:33
  
  Hi Jeff,
  
  This restriction is imposed by SharePoint. SharePoint does not perform any operation on Lists having more than 5000 items. Even if we specify group limit, it first has to perform grouping on the given items before applying the limit. Since, it’s an expensive process, in an online environment it’s not directly possible.
  
  So, you can try two things: –
  * Reduce the number of items by applying some filtering in the CAML. Do you really need to see all the items? If not then, remove the unnecessary items.
  * Download all the items in batches and then, apply grouping through your code.
  
  LikeLike
  
  Reply
Jeff says:

September 12, 2017 at 15:16

Thanks, Piyush

Finally, I decided to implement the grouping through my code.:)

LikeLike

Reply
- Piyush says:
  
  September 14, 2017 at 15:14
  
  Unfortunately, it’s a very strict restriction imposed by Microsoft on SharePoint Online! The sole purpose of this post was to leverage the in-built features of SharePoint. However, on List with more than 5000 items they simply block a lot of operations. 😐
  
  LikeLike
  
  Reply
Алексей Алексеев says:

October 17, 2017 at 13:48

Hi. How can I group, for example, blogs by author? GroupBy result consists only .COUNT.group field that seems useful.Row array is a flat collection of items and totally unuseful. I expects something like this:
{
groups:{
AuthorID:{
Blogs:[]
AuthorInfo:
},
AuthorID:{
Blogs:[]
AuthorInfo:
},
……
}
}

Is it possible?

LikeLike

Reply
Snehal Shelke says:

April 6, 2018 at 14:39

Works great for but me. but want to get the count from the result

LikeLike

Reply
- Piyush says:
  
  April 6, 2018 at 18:00
  
  Hey. In order to get the Count aggregation, you need to use the type, COUNT in your Aggregation. In the above sample, I have used the type, SUM. However, you can use any type described in the table above.
  PS: If you use count and say, the name of the field is, Profit then, you’ll find your result in, Profit.COUNT.agg field.
  
  LikeLike
  
  Reply
Richard says:

April 26, 2018 at 19:27
Hi thanks again for your post. I’ve since now upgraded to 2013 and realised that I could now do this! After I bit of head banging the function started to come out with the goods. My problem is that I’m getting duplicates.
I know it’s down to the fact that the field I want to group on is a lookup field because my code works when I switch it to a ‘standard’ field.
Could you have a look maybe see where I’m going wrong.

My caml and code is
CamlQuery test = new CamlQuery(); test.ViewXml = @"
1000002 Hidden 30 <Aggregations > <FieldRef Name='Doc_x002e__x0020_Type_x003A_Section' Type='COUNT'/> </Aggregations> </View>"; test.FolderServerRelativeUrl = "/Drafts/Documents/1000002"; //Idea for aggregations var l = _sp.Web.Lists.GetByTitle('Documents'); var q = l.RenderListData(test.ViewXml); _sp.ExecuteQuery(); return Content(q.Value);

There are lots of rows but here is a snippet

{ "Row" : [{ "Doc_x002e__x0020_Type_x003A_Section": "Advanced Decisions\u002fStatements", "ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')", "Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23Advanced%20Decisions%2FStatements%3B%23", "Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "1", "Doc_x002e__x0020_Type_x003A_Section.newgroup": "1", "Doc_x002e__x0020_Type_x003A_Section.groupindex": "1_", "Doc_x002e__x0020_Type_x003A_Section.COUNT": "140", "Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "1" } ,{ "Doc_x002e__x0020_Type_x003A_Section": "AHP - Acorn Further Education Centre", "ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')", "Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Acorn%20Further%20Education%20Centre%3B%23", "Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "3", "Doc_x002e__x0020_Type_x003A_Section.newgroup": "1", "Doc_x002e__x0020_Type_x003A_Section.groupindex": "2_", "Doc_x002e__x0020_Type_x003A_Section.COUNT": "140", "Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "3" } ,{ "Doc_x002e__x0020_Type_x003A_Section": "AHP - Acorn Further Education Centre", "ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')", "Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Acorn%20Further%20Education%20Centre%3B%23", "Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "3", "Doc_x002e__x0020_Type_x003A_Section.newgroup": "", "Doc_x002e__x0020_Type_x003A_Section.groupindex": "2_", "Doc_x002e__x0020_Type_x003A_Section.COUNT": "140", "Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "3" } ,{ "Doc_x002e__x0020_Type_x003A_Section": "AHP - Arts and Music Therapies", "ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')", "Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Arts%20and%20Music%20Therapies%3B%23", "Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "10", "Doc_x002e__x0020_Type_x003A_Section.newgroup": "1", "Doc_x002e__x0020_Type_x003A_Section.groupindex": "3_", "Doc_x002e__x0020_Type_x003A_Section.COUNT": "140", "Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "10" } ,{ "Doc_x002e__x0020_Type_x003A_Section": "AHP - Arts and Music Therapies", "ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')", "Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Arts%20and%20Music%20Therapies%3B%23", "Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "10", "Doc_x002e__x0020_Type_x003A_Section.newgroup": "", "Doc_x002e__x0020_Type_x003A_Section.groupindex": "3_", "Doc_x002e__x0020_Type_x003A_Section.COUNT": "140", "Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "10" } ,{ "Doc_x002e__x0020_Type_x003A_Section": "AHP - Arts and Music Therapies", "ecb.dispex": "return DispEx(this,event,'TRUE','FALSE','','','1','','','','','2','1','','','','')", "Doc_x002e__x0020_Type_x003A_Section.urlencoded": "%3B%23AHP%20%2D%20Arts%20and%20Music%20Therapies%3B%23", "Doc_x002e__x0020_Type_x003A_Section.COUNT.group": "10", "Doc_x002e__x0020_Type_x003A_Section.newgroup": "", "Doc_x002e__x0020_Type_x003A_Section.groupindex": "3_", "Doc_x002e__x0020_Type_x003A_Section.COUNT": "140", "Doc_x002e__x0020_Type_x003A_Section.COUNT.agg": "10" }

As you can see I’m getting some duplicate rows.
Any help would be great.

Thanks
Richard

LikeLike
Reply
- Piyush says:
  
  April 29, 2018 at 22:43
  
  Hi Richard,
  Can you try with, GroupBY on, Doc_x002e__x0020_Type_x003A_Section and in Aggregations, use COUNT on the ID field. Also, for Aggregations, use the attribute, Value="On" as shown above.
  
  LikeLike
  
  Reply
  - Richard says:
    
    April 30, 2018 at 19:17
    
    Hi thanks, but seems no matter what I try it doesn’t want to work.
    I’ve even tried a filter on the folder using the standard where – but no joy.
    
    I’m pretty sure it’s down to the folder and the location which you don’t seem to be able to send. I’ve also got a strong feeling that it won’t work on SP2013 and needs 2016/online to work.
    I’ve got a stackoverflow question here https://sharepoint.stackexchange.com/questions/240764/csom-hightrust-sp2013-group-by-aggregate-giving-duplicates which might be better place if you have any more ideas.
    
    LikeLike
ram says:

May 30, 2018 at 20:41

Hi pankaj,

Could you please tell me how to display the Profit.SUM.agg”: “\u20b9 157.44” and title in page

Thanks

LikeLike

Reply
NAVEEN KUMAR K says:

September 12, 2018 at 17:41

You saved my life!!!

LikeLike

Reply
NAVEEN KUMAR K says:

September 13, 2018 at 13:32

viewfields is not working

LikeLike

Reply
John Bonfardeci says:

May 8, 2019 at 21:59

This was so helpful. Thank you!

LikeLike

Reply

	José Miguel on Call Microsoft Flow from an Ex…
	Shahar on Register App in SharePoin…
	keith on Update the child flow for acti…
	Chris on Temporarily disable Power Auto…
	KpR on Download Large Files from Shar…

Piyush K Singh

unfolding the journey

SharePoint ListItems Group By

CSOM

App/Add-In

JSOM

CAML

Si No.

Total Function

Function Type

Key Takeaway

30 thoughts on “SharePoint ListItems Group By”

Leave a comment Cancel reply

CSOM

App/Add-In

JSOM

CAML

Si No.

Total Function

Function Type

Key Takeaway

Share:

Related

30 thoughts on “SharePoint ListItems Group By”

Leave a comment Cancel reply