Sum of column values in sharepoint list if value for another column matches using C# and CAML

Question

I am trying to get the value of manager in the sharepoint list and when the manager values are the same, i'm trying to add all the amounts then output the manager name and the sum.

Here are the entries in the sharepoint list

Manager     Amount
1           1000
2           2000
3           3000
4           500
1           1500
2           2500

Then I should send an email that should have this output and return only the top 3 highest amounts:

Manager     Amount
2           4500
3           3000
1           2500

Here is my CAML Query

camlQuery.ViewXml = " <Query><Where><And><IsNotNull><FieldRef Name='Manager' /></IsNotNull><IsNotNull><FieldRef Name='Amount' /></IsNotNull></And><OrderBy><FieldRef Name='Amount' Ascending='False' /></OrderBy></Where></Query> ";

And Here is my Code

        double iSum = 0;

        foreach (ListItem oListItem in collListItem)
        {
            FieldUserValue man = (FieldUserValue)oListItem["Manager"];
                if(oListItem["Amount"].ToString() == null)
                continue;
                iSum += Convert.ToDouble(oListItem["Amount"].ToString());

            Console.WriteLine("\nManager Name: " + man.LookupValue + " Amount: " + iSum.ToString());
            message += "<tr>"
                     + " <td class='pdetails'> " + man.LookupValue + "</td> "
                     + " <td class='pdetails'> " + iSum.ToString() + "</td></tr> ";
        }

Kindly help me in fixing my caml query and foreach loop and how to get the expected output which is the sum of the amounts per manager. Please and thanks.

Answer 1

I don't have SharePoint handy, so the list access code might not be quite right, but the rest of the code should do what you want.

ManagerSummary summary = new ManagerSummary();
foreach (ListItem oListItem in collListItem)
{
    FieldUserValue managerValue = (FieldUserValue)oListItem["Manager"];
    string managerId = managerValue.LookupId;
    string managerName = managerValue.LookupValue;
    double amount = Convert.ToDouble(oListItem["Amount"].ToString());

    summary.AddValue(managerId, managerName, amount);
}

StringBuilder output = new StringBuilder();
output.AppendLine("<tr><th>Manager Name</th><th>Total</th></tr>");

foreach (var manager in summary.TopManagers(3))
{
    output.AppendFormat("<tr><td>{0}</td><td>{1}</td></tr>", manager.Name, manager.Total);
    output.AppendLine();
}

Console.WriteLine(output.ToString());


class ManagerSummary
{
    Dictionary<string, Manager> _managers = new Dictionary<string, Manager>();

    public void AddValue(string managerId, string managerName, double amount)
    {
        if (_managers.ContainsKey(managerId))
        {
            _managers[managerId].Total += amount;
        }
        else
        {
            _managers[managerId] = new Manager { Id = managerId,  Name = managerName, Total = amount };
        }
    }

    public List<Manager> TopManagers(int count)
    {
        var managers = _managers.Values.ToList();
        return managers.OrderByDescending(x => x.Total).Take(count).ToList();
    }


}

class Manager
{
    public string Id { get; set; }
    public string Name { get; set; }
    public double Total { get; set; }

    public override string ToString()
    {
        return string.Format("{0,-20}{1,-10}", Name, Total);
    }
}

Answer 2

You can use the LINQ GroupBy to handle the grouping of managers for you, as well as the summing of all of the ammounts within a group.

var query = collListItem.OfType<ListItem>()
        .Select(item => new
        {
            Manager = (FieldUserValue)item["Manger"],
            Amount = item["Amount"] as double?,
        })
        .GroupBy(item => item.Manager.LookupValue)
        .Select(group => new
        {
            Manager = group.Key,
            Amount = group.Sum(item => item.Amount),
        })
        .Select(group => string.Format(
@"<tr><td class='pdetails'>{0}</td>
<td class='pdetails'>{1}</td></tr>", group.Manager, group.Amount));

string message = string.Concat(query);