This post is about directly applying aggregations on a SharePoint List, without specifying any grouping condition. If you want to group SharePoint List data before applying aggregation then, refer to this post, SharePoint Group By.

I must admit that this turned out to be little tricky than what I had imagined. The situation was fairly simple. I had a SharePoint List. I had applied couple of Totals functions, Standard Deviation & Variance, on one of its Number field in two separate custom views. I just wanted to retrieve those values from client side. That’s it! My initial assessment was there must be some function/property that I can easily query from the client. As it seemed initially, there’s no direct way of doing that. Fine, just another day at the office for a SharePoint developer 😉

What irked me was when I googled the issue, almost all the responses were like it’s not possible! The value is never stored. SharePoint calculates it on the fly, and then it displays the result on the View Page using its XSLT. The only possible solution was to retrieve all the items first from the SP, and then perform your calculation like, Sum, Avg, etc. on the client side. Though technically, the approach was correct but, somehow it didn’t appeal to me for the following reasons:

• Doing the same calculation on the client-side which SP was already doing seemed pretty weird.
• It could result in huge performance overhead considering your List has large number of items or it could grow exponentially in the foreseeable future. Downloading all the items in that case and then looping through all of them to calculate the Sum, Avg, etc., would only make things slower and slower.

I was somehow, still not convinced that it’s not possible. If SharePoint is calculating it, why should I have to re-calculate the same? Couldn’t I just get the value from the SharePoint itself? I then looked into the schema of one of the custom view.

<View Name="{44A587B8-4ABD-45F5-BF14-C2FF52E28CD8}" MobileView="TRUE" Type="HTML" DisplayName="CalulatedCol" Url="/sites/pkteamSite1/Lists/Sales Order Details/CalulatedCol.aspx" Level="1" BaseViewID="1" ContentTypeID="0x" ImageUrl="/_layouts/15/images/generic.png?rev=23" >
	<Query />
	<ViewFields>
		<FieldRef Name="Attachments" />
		<FieldRef Name="OrderID" />
		<FieldRef Name="LinkTitle" />
		<FieldRef Name="Supplier_x0020_Name" />
		<FieldRef Name="Category_x0020_Name" />
		<FieldRef Name="Customer_x0020_Name" />
		<FieldRef Name="Customer_x0020_City" />
		<FieldRef Name="Customer_x0020_Country" />
		<FieldRef Name="Customer_x0020_Continent" />
		<FieldRef Name="Employee_x0020_Name" />
		<FieldRef Name="Employee_x0020_Designation" />
		<FieldRef Name="Employee_x0020_City" />
		<FieldRef Name="Employee_x0020_Country" />
		<FieldRef Name="Employee_x0020_Continent" />
		<FieldRef Name="OrderDate" />
		<FieldRef Name="RequiredDate" />
		<FieldRef Name="ShippedDate" />
		<FieldRef Name="ShipCity" />
		<FieldRef Name="ShipRegion" />
		<FieldRef Name="ShipCountry" />
		<FieldRef Name="Ship_x0020_Continent" />
		<FieldRef Name="Total_x0020_Purchase_x0020_Amoun" />
		<FieldRef Name="Prod_x0020_Name_x0020_Calc" />
		<FieldRef Name="Half_x0020_Purchase" />
	</ViewFields>
	<RowLimit Paged="TRUE">30</RowLimit>
	<Aggregations Value="On">
		<FieldRef Name="OrderID" Type="VAR" />
	</Aggregations>
	<JSLink>clienttemplates.js</JSLink>
	<XslLink Default="TRUE">main.xsl</XslLink>
	<Toolbar Type="Standard"/>
</View>

Notice the little Aggregations tag. That was like, the first ray of hope. Aggregations seemed like the possible solution ’cause it was carrying the function definition, VAR, for VARIANCE.

Wrong Approach

So I ran the following code, with the Aggregations tag in the ViewXml, to get the Standard Deviation of the numeric field, OrderID:

using (SP.ClientContext ctx = new SP.ClientContext("http://dktp-piyush:48694/sites/pkteamSite1"))
{
	SP.List list = ctx.Web.Lists.GetByTitle("Sales Order Details");
	SP.CamlQuery query = new SP.CamlQuery();
	query.ViewXml = "1";
	SP.ListItemCollection items = list.GetItems(query);
	ctx.Load(items);
	ctx.ExecuteQuery();
}

I only received the first ListItem with its properties but no Standard Deviation! If I don’t mention the  RowLimit then, all the ListItems were being retrieved. I was definitely taking the wrong approach ’cause I only needed the aggregate function result and not the entire set of ListItems.

Correct Approach

I knew, that the approach had to be altered. CAML query is the answer but you can’t get a single calculated result from GetItems. I then started looking at various other methods that the List offers and then, I came across the method, RenderListData. It returns a ClientResult (read JSON) and accepts viewXml (read CAML) as parameter. So I ran the following code:

using (SP.ClientContext ctx = new SP.ClientContext("http://dktp-piyush:48694/sites/pkteamSite1"))
{
	SP.List list = ctx.Web.Lists.GetByTitle("Sales Order Details");

	//STDEV
	string stdDev = "<View><ViewFields><FieldRef Name=\"OrderID\"/></ViewFields><RowLimit>1</RowLimit><Aggregations><FieldRef Name=\"OrderID\" Type=\"STDEV\" /></Aggregations></View>";

	//VAR
	string strVar = "<View><ViewFields><FieldRef Name=\"OrderID\"/></ViewFields><RowLimit>1</RowLimit><Aggregations><FieldRef Name=\"OrderID\" Type=\"VAR\" /><FieldRef Name=\"OrderDate\" Type=\"COUNT\" /></Aggregations></View>";
	SP.ClientResult<string> varView = list.RenderListData(strVar);
	SP.ClientResult<string> stdDevView = list.RenderListData(stdDev);
	ctx.ExecuteQuery();
}

And voila! It’s done! 😎
The output for both the queries were:

stdDev::
{ "Row" :
[{
"ID": "1",
"PermMask": "0x7fffffffffffffff",
"FSObjType": "0",
"OrderID": "10,248",
"OrderID.": "10248.0000000000",
"<strong>OrderID.STDEV</strong>": "176.488030425185"
}
],"FirstRow" : 1,
"LastRow" : 1
,"FilterLink" : "?"
,"ForceNoHierarchy" : "1"
,"HierarchyHasIndention" : ""}

strVar::
{ "Row" :
[{
"ID": "1",
"PermMask": "0x7fffffffffffffff",
"FSObjType": "0",
"OrderID": "10,248",
"OrderID.": "10248.0000000000",
"<strong>OrderID.VAR</strong>": "31,148.024883361",
"<strong>OrderDate.COUNT</strong>": "1599"
}
],"FirstRow" : 1,
"LastRow" : 1
,"FilterLink" : "?"
,"ForceNoHierarchy" : "1"
,"HierarchyHasIndention" : ""}

Look at the values, OrderID.VAR, OrderID.STDEV, & OrderDate.COUNT. The count field reveals that the List had 1599 items. Although, some fields of the first item were retrieved due to the RowLimit being set to 1, still the response, I would say, was crisp and to the point only instead of the entire ListItemCollection which seemed to be the only option earlier. The VAR (Variance) & STDEV (Standard Deviation) can be verified from the screen-shots below

Std Deviation

Variance

Here’s the list of all the Totals function along with their TYPE values. To fetch the result of any of the Totals function just, use the corresponding Type value in the Aggregations property.

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

Footnotes

• Using CSOM, I have fetched these values in a C# console application. Though, I haven’t tried the JavaScript approach, I don’t see why the same won’t work there considering that the RenderListData method is available in both, REST API and sp.js.
• All these calculations, in this blog, has been carried out on the “Sales Order Details” table of the AdventureWorks-2012 database.