Get sum of two columns in one LINQ query

let's say that I have a table called Items (ID int, Done int, Total int)

I can do it by two queries:

int total = m.Items.Sum(p=>p.Total)
int done = m.Items.Sum(p=>p.Done)

But I'd like to do it in one query, something 开发者_运维知识库like this:

var x = from p in m.Items select new { Sum(p.Total), Sum(p.Done)};

Surely there is a way to call aggregate functions from LINQ syntax...?

This will do the trick:

LINQ Query Syntax:

from p in m.Items
group p by 1 into g
select new
{
    SumTotal = g.Sum(x => x.Total), 
    SumDone = g.Sum(x => x.Done) 
};

LINQ Method Syntax:

m.Items
 .GroupBy(r => 1)
 .Select(g => new 
 {
     SumTotal = g.Sum(x => x.Total), 
     SumDone = g.Sum(x => x.Done) 
 });

Notice that this solution (both syntax) returns a list of 1 item, so you might want to add at the end:

 .FirstOrDefault();

To sum the table, group by a constant:

from p in m.Items
group p by 1 into g
select new {
    SumTotal = g.Sum(x => x.Total),
    SumDone = g.Sum(x => x.Done)
}

How about

   m.Items.Select(item => new { Total = item.Total, Done = item.Done })
          .Aggregate((t1, t2) => new { Total = t1.Total + t2.Total, Done = t1.Done + t2.Done });

Figuring out where to extract the sums or other aggregate in the rest of my code confused me, until I remembered that the variable I constructed was an Iqueryable. Suppose we have a table in our database composed of Orders, and we want to produce a summary for the ABC company:

var myResult = from g in dbcontext.Ordertable
               group p by (p.CUSTNAME == "ABC") into q  // i.e., all of ABC company at once
               select new
{
    tempPrice = q.Sum( x => (x.PRICE ?? 0m) ),  // (?? makes sure we don't get back a nullable)
    tempQty = q.Sum( x => (x.QTY ?? 0m) )
};

Now the fun part -- tempPrice and tempQty aren't declared anywhere but they must be part of myResult, no? Access them as follows:

Console.Writeline(string.Format("You ordered {0} for a total price of {1:C}",
                                 myResult.Single().tempQty,
                                 myResult.Single().tempPrice ));

A number of other Queryable methods could be used as well.

With a helper tuple class, either your own or—in .NET 4—the standard ones you can do this:

var init = Tuple.Create(0, 0);

var res = m.Items.Aggregate(init, (t,v) => Tuple.Create(t.Item1 + v.Total, t.Item2 + v.Done));

And res.Item1 is the total of the Total column and res.Item2 of the Done column.

This has been answered already, but the other answers will still do multiple iterations over the collection (multiple calls to Sum) or create lots of intermediate objects/Tuples which may be fine, but if it isn't, then you can create an extension method (or multiple) that does it the old-fashioned way but fits well in a LINQ expression.

Such an extension method would look like this:

public static Tuple<int, int> Sum<T>(this IEnumerable<T> collection, Func<T, int> selector1, Func<T, int> selector2)
{
    int a = 0;
    int b = 0;

    foreach(var i in collection)
    {
        a += selector1(i);
        b += selector2(i);
    }

    return Tuple.Create(a, b);
}

And you can use it like this:

public class Stuff
{
    public int X;
    public int Y;
}

//...

var stuffs = new List<Stuff>()
{
    new Stuff { X = 1, Y = 10 }, 
    new Stuff { X = 1, Y = 10 }
};

var sums = stuffs.Sum(s => s.X, s => s.Y);

//Calculate the total in list field values
//Use the header file: 

Using System.Linq;
int i = Total.Sum(G => G.First);

//By using LINQ to calculate the total in a list field,

var T = (from t in Total group t by Total into g select g.Sum(t => t.First)).ToList();

//Here Total is a List and First is the one of the integer field in list(Total)

Using the language support for tuples introduced in C# 7.0 you can solve this using the following LINQ expression:

var itemSums = m.Items.Aggregate((Total: 0, Done: 0), (sums, item) => (sums.Total + item.Total, sums.Done + item.Done));

Full code sample:

var m = new
{
    Items = new[]
    {
        new { Total = 10, Done = 1 },
        new { Total = 10, Done = 1 },
        new { Total = 10, Done = 1 },
        new { Total = 10, Done = 1 },
        new { Total = 10, Done = 1 },
    },
};

var itemSums = m.Items.Aggregate((Total: 0, Done: 0), (sums, item) => (sums.Total + item.Total, sums.Done + item.Done));

Console.WriteLine($"Sum of Total: {itemSums.Total}, Sum of Done: {itemSums.Done}");

When you use group by Linq creates a new collection of items so you have two collections of items.

Here's a solution to both problems:

1. summing any amount of members in one iteration and
2. avoid duplicating your item's collection

Code:

public static class LinqExtensions
{
  /// <summary>
  /// Computes the sum of the sequence of System.Double values that are obtained 
  /// by invoking one or more transform functions on each element of the input sequence.
  /// </summary>
  /// <param name="source">A sequence of values that are used to calculate a sum.</param>
  /// <param name="selectors">The transform functions to apply to each element.</param>    
  public static double[] SumMany<TSource>(this IEnumerable<TSource> source, params Func<TSource, double>[] selectors)
  {
    if (selectors.Length == 0)
    {
      return null;
    }
    else
    {
      double[] result = new double[selectors.Length];

      foreach (var item in source)
      {
        for (int i = 0; i < selectors.Length; i++)
        {
          result[i] += selectors[i](item);
        }
      }

      return result;
    }
  }

  /// <summary>
  /// Computes the sum of the sequence of System.Decimal values that are obtained 
  /// by invoking one or more transform functions on each element of the input sequence.
  /// </summary>
  /// <param name="source">A sequence of values that are used to calculate a sum.</param>
  /// <param name="selectors">The transform functions to apply to each element.</param>
  public static double?[] SumMany<TSource>(this IEnumerable<TSource> source, params Func<TSource, double?>[] selectors) 
  { 
    if (selectors.Length == 0)
    {
      return null;
    }
    else
    {
      double?[] result = new double?[selectors.Length];

      for (int i = 0; i < selectors.Length; i++)
      {
        result[i] = 0;
      }

      foreach (var item in source)
      {
        for (int i = 0; i < selectors.Length; i++)
        {
          double? value = selectors[i](item);

          if (value != null)
          {
            result[i] += value;
          }
        }
      }

      return result;
    }
  }
}

Here's the way you have to do the summation:

double[] result = m.Items.SumMany(p => p.Total, q => q.Done);

Here's a general example:

struct MyStruct
{
  public double x;
  public double y;
}

MyStruct[] ms = new MyStruct[2];

ms[0] = new MyStruct() { x = 3, y = 5 };
ms[1] = new MyStruct() { x = 4, y = 6 };

// sum both x and y members in one iteration without duplicating the array "ms" by GROUPing it
double[] result = ms.SumMany(a => a.x, b => b.y);

as you can see

result[0] = 7 
result[1] = 11