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”).