Posted by: R Manimaran | May 21, 2011

SharePoint List: Find Sum, Average on the values with Group By

SharePoint List: Find Sum, Average on the values with Group By

 Below is my SharePoint List Sales which contains the sales details for each country.  It also has a Status column which contains values as “Open” / “Closed”.

Here I like to have the sum of the sales values group by the country column.

When I checked is there any way to do with CAML query, I found there is attribute called Aggregate in the CAML. But it is related to View in the SharePoint list.  But how to use it in CAML query?

I cannot able to find any way to achieve this using CAML.  I Checked with Lambda expression and got the required output.

SPList myList = web.Lists.Cast<SPList>().FirstOrDefault(item => item.Title == "Sales");

if (myList != null)

{

IEnumerable<object> itemsDistinct = myList.Items.Cast<SPListItem>()

.Where(item => Convert.ToString(item["Status"]) == "Open")

.Select(item => item["Title"])

.Distinct();

using (IEnumerator<object> enumerator = itemsDistinct.GetEnumerator())

{

Console.WriteLine("----------------------------------------");

Console.WriteLine("Country\t\tSales Sum\tAverage");

Console.WriteLine("----------------------------------------");

while (enumerator.MoveNext())

{

string country = enumerator.Current.ToString();

int salesSum = myList.Items.Cast<SPListItem>()

.Where(item => item[SPBuiltInFieldId.Title].ToString() == country && Convert.ToString(item["Status"]) == "Open")

.Sum(t => Convert.ToInt32(t["Sales"]));

double salesAverage = myList.Items.Cast<SPListItem>()

.Where(item => item[SPBuiltInFieldId.Title].ToString() == country)

.Average(t => Convert.ToInt32(t["Sales"]));

Console.WriteLine(string.Format("{0}\t\t{1}\t\t{2}",country,salesSum,salesAverage));

}

int totalSum = myList.Items.Cast<SPListItem>()

.Where(item=>Convert.ToString(item["Status"])=="Open")

.Sum(item => Convert.ToInt32(item["Sales"]));

Console.WriteLine("----------------------------------------");

Console.WriteLine(string.Format("{0}\t\t{1}","Total",totalSum));

Console.WriteLine("----------------------------------------");

}

}

My output is 


Note: Here the sum is calculated based on the status column(Status=”Open”).

Advertisements

Responses

  1. HI

    After lot of searching in google just came across your site can you please help me with this..

    on the top in your example you have
    Country sales and Status..(open ,close…)

    Similarly i have a list as assets

    with Asset category — Asset Status and count

    I need my list to be shown as
    Asset Category—- Asset Status—– Count

    Desktops Inuse 50
    laptops Instore 15

    HOw can I achieve this in sharepoint 2010 ?

  2. Awesome..!!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: