Select multiple fields group by and sum

Question

I want to do a query with linq (list of objects) and I really don't know how to do it, I can do the group and the sum but can't select rest of the fields. Example:

ID  Value     Name   Category
1   5         Name1  Category1  
1   7         Name1  Category1
2   1         Name2  Category2
3   6         Name3  Category3
3   2         Name3  Category3

I want to group by ID, SUM by Value and return all fields like this.

ID  Value     Name   Category
1   12        Name1  Category1  
2   1         Name2  Category2
3   8         Name3  Category3

Do you have check ? http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b and your case: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — Aristos, Apr 19 '13 at 09:01
since all the fields seems unique for each ID, why don't you include them in the group as well. — Habib, Apr 19 '13 at 09:01
go through this link.. http://stackoverflow.com/questions/847066/group-by-multiple-columns hope this will solve your problem — Utkarsh Patel, Apr 19 '13 at 10:43

score 121 · Accepted Answer · edited Nov 04 '14 at 16:16

121

Updated : If you're trying to avoid grouping for all the fields, you can group just by Id:

data.GroupBy(d => d.Id)
    .Select(
        g => new
        {
            Key = g.Key,
            Value = g.Sum(s => s.Value),
            Name = g.First().Name,
            Category = g.First().Category 
        });

But this code assumes that for each Id, the same Name and Category apply. If so, you should consider normalizing as @Aron suggests. It would imply keeping Id and Value in one class and moving Name, Category (and whichever other fields would be the same for the same Id) to another class, while also having the Id for reference. The normalization process reduces data redundancy and dependency.

edited Nov 04 '14 at 16:16

davmos

9,324
4
40
43

answered Apr 19 '13 at 09:17

Diana Ionita

3,251
3
27
42

Yes I know it but just dont want to group all fields, would be a differnt way, I try with first() but then how to sum? – user2112420 Apr 19 '13 at 09:28
Yes I know what are you saying, I have my DB normalize, it is just a result of my query, but for each lines of my query I have repeat it values, and I want agroup them and sum the Value. – user2112420 Apr 19 '13 at 09:50

score 4 · Answer 2 · answered Apr 19 '13 at 09:09

4

void Main()
{
            //Me being lazy in init
    var foos = new []
    {
        new Foo { Id = 1, Value = 5},
        new Foo { Id = 1, Value = 7},
        new Foo { Id = 2, Value = 1},
        new Foo { Id = 3, Value = 6},
        new Foo { Id = 3, Value = 2},
    };
    foreach(var x in foos)
    {
        x.Name = "Name" + x.Id;
        x.Category = "Category" + x.Id;
    }
            //end init.

    var result = from x in foos
                group x.Value by new { x.Id, x.Name, x.Category}
                into g
                select new { g.Key.Id, g.Key.Name, g.Key.Category, Value = g.Sum()};
    Console.WriteLine(result);
}

// Define other methods and classes here
public class Foo
{
    public int Id {get;set;}
    public int Value {get;set;}

    public string Name {get;set;}
    public string Category {get;set;}   
}

answered Apr 19 '13 at 09:09

Aron

15,464
3
31
64

I know this way, but I dont want to have in my group so many fields, I have more that 7. – user2112420 Apr 19 '13 at 09:14
@user2112420 Then you are going to have to normalise your class. – Aron Apr 19 '13 at 09:15
It is a result query I do from multiple table and create a object this type. – user2112420 Apr 19 '13 at 09:30
I just do my query from different tables, the result would be type an Object, with that query I have create a list of my objects, because in each row I have ID,Name,Category,Value, etc. – user2112420 Apr 19 '13 at 09:32
How do you get you results from these tables? If you are using an ORM you should be able to get good normalized object trees. – Aron Apr 19 '13 at 10:07
Yes I use EF, and most of the fields I need are navigation properties. – user2112420 Apr 19 '13 at 10:23

score 2 · Answer 3 · answered Jul 30 '18 at 08:43

If your class is really long and you don't want to copy all the stuff, you can try something like this:

l.GroupBy(x => x.id).
  Select(x => {
    var ret = x.First();
    ret.value = x.Sum(xt => xt.value);
    return ret;
  }).ToList();

With great power great responsibility comes. You need to be careful. Line ret.value = x.Sum(xt => xt.value) will change your original collection, as you are passing reference, not new object. If you want to avoid it, you need to add some Clone method into your class like MemberwiseClone (but again, this will create shallow copy, so be careful). Afer that just replace the line with: var ret = x.First().Clone();

score 1 · Answer 4 · answered Apr 19 '13 at 09:14

try this:

var objList = new List<SampleObject>();

objList.Add(new SampleObject() { ID = 1, Value = 5, Name = "Name1", Category = "Catergory1"});
objList.Add(new SampleObject() { ID = 1, Value = 7, Name = "Name1", Category = "Catergory1"});
objList.Add(new SampleObject() { ID = 2, Value = 1, Name = "Name2", Category = "Catergory2"});
objList.Add(new SampleObject() { ID = 3, Value = 6, Name = "Name3", Category = "Catergory3"});
objList.Add(new SampleObject() { ID = 3, Value = 2, Name = "Name3", Category = "Catergory3"});

var newList = from val in objList
              group val by new { val.ID, val.Name, val.Category } into grouped
              select new SampleObject() { ID = grouped.ID, Value = grouped.Sum(), Name = grouped.Name, Category = grouped.Category };

to check with LINQPad:

newList.Dump();

Select multiple fields group by and sum

4 Answers4

Linked