Group By, Sum in LINQ

Question

suppose I have the following table:

user type amount
2    a    10
2    b    20
2    c    40
3    a    15
3    b    20
3    c    45

I want to replace (c) amount with (c - (a+b)) grouping by the user and type, how could I do this? thanks

Answer 1

I would suggest an expansion on Steven's answer, since that will only return a sequence of scalar c values and not keep them associated with the users or the a and b amounts. Try this:

var query1 = from i in table
             group i by i.user
             into g
             let a = g.Where(t => t.type == "a").Sum(t => t.amount)
             let b = g.Where(t => t.type == "b").Sum(t => t.amount)
             let c = g.Where(t => t.type == "c").Sum(t => t.amount)
                   - (a + b)
             select new {user = g.Key, a, b, c};

Then, if you want to convert it back to tabular form:

var query2 = query1.SelectMany(q => new List<TableType>
                                    {
                                        new TableType(q.user, "a", q.a),
                                        new TableType(q.user, "b", q.b),
                                        new TableType(q.user, "c", q.c)
                                    })
                                    .OrderBy(t => t.user)
                                    .ThenBy(t => t.type);

Note: I've tested this and it works.

Answer 2

Here is a way:

UPDATE (now using Sum):

from item in table
group item by item.user into g
let a = (from i in g where i.type == "a" select i.amount).Sum()
let b = (from i in g where i.type == "b" select i.amount).Sum()
let c = (from i in g where i.type == "c" select i.amount).Sum()
select c - (a + b);