How to write a LINQ query combining group by and aggregates?

Question

Given the following input, how do I write a LINQ query or expression to return an aggregated result set for the quantity?

Input:

var foo = new[] { new { PO = "1", Line = 2, QTY = 0.5000 }, 
                  new { PO = "1", Line = 2, QTY = 0.2500 }, 
                  new { PO = "1", Line = 2, QTY = 0.1000 }, 
                  new { PO = "1", Line = 2, QTY = -0.1000 } 
                }.ToList();

Desired result:

Something along the lines of

new { PO = "1", Line = 2, QTY = 0.7500 } // .5 + .25 + .1 + -.1

How would I write it for multiple lines as well (see the object model in foo)?

BrokenGlass · Accepted Answer · 2011-05-06T00:11:24.127

5

How about this:

var result = foo.GroupBy(x => x.Line)
                .Select(g => new { PO = g.First().PO, 
                                   Line = g.Key, 
                                   QTY = g.Sum(x => x.QTY) });

In the case you just have one Line, just add a .Single() - result is an IEnumerable of the anonymous type defined when you set up foo.

Edit:

If both PO and Line should designate different groups (PO can have different values), they both have to be part of the group key:

var result = foo.GroupBy(x => new { x.PO, x.Line})
                .Select(g => new { 
                    PO = g.Key.PO, 
                    Line = g.Key.Line, 
                    QTY = g.Sum(x => x.QTY) 
                  });

edited May 06 '11 at 00:11

answered May 05 '11 at 23:57

BrokenGlass

158,293
28
286
335

1

You are grouping on just one x.Line. He needs to group on both x.PO and x.Line – Priyank May 06 '11 at 00:04
@Priyank: question states he has multiple `Line`'s not multiple PO's - otherwise you would be correct, it's a matter of interpretation I guess. I edited that in. – BrokenGlass May 06 '11 at 00:07
In this case, I didn't really care about the PO but it's nice to see both solutions. This should be enough to get my brain over the hump. Thanks a lot! – longda May 06 '11 at 00:14

score 1 · Answer 2 · answered May 06 '11 at 00:01

var query = (from t in foo
                         group t by new {t.PO, t.Line}
                         into grp
                             select new
                                        {
                                            grp.Key.PO,
                                            grp.Key.Line,
                                            QTY = grp.Sum(t => t.QTY)
                                        }).ToList()

How to write a LINQ query combining group by and aggregates?

2 Answers2

Linked