none
Linq Group by more than one field and sum others

    Question

  • I'm a newbie to Linq so sorry if this is simple...

    Given the following list of this object I want to group by on a few properties and sum the rest. Is this possible?

      class Test
      {
        public string PropA { get; set; }
        public int PropB { get; set; }
        public double PropC { get; set; }
        public double PropD { get; set; }
      }

    I want to group by PropA, PropB and sum(PropC), sum(PropD)

    the sql would be

    select PropA, PropB, sum(PropC), sum(PropD) from mytable
    group by PropA, PropB

    Can this be done?

    Thanks.

    Wednesday, April 28, 2010 7:21 PM

Answers

  • Hi Jbassking,

    I fully agree with Deborah.
    If you prefer the query comprehension language, you can also use this snippet:

    var query = from t in testList
          group t by new { t.PropA, t.PropB } into g
          select new { CustomerType = g.Key.PropA, 
            CustomerTypeId = g.Key.PropB,
            Total1 = g.Sum(c => c.PropC),
            Total2 = g.Sum(c => c.PropD)
          };
    

    Marcel

    • Marked as answer by Jbassking Thursday, April 29, 2010 2:04 PM
    Thursday, April 29, 2010 8:02 AM

All replies

  • This worked for me:

          List<Test> testList = new List<Test>();
    
          testList = new List<Test>
              {new Test() 
                 { 
                  PropA="Customer", 
                  PropB = 1, 
                  PropC = 10, 
                  PropD = 20}, 
              new Test() 
                 { 
                  PropA="Employee", 
                  PropB = 2, 
                  PropC = 15, 
                  PropD = 25}, 
              new Test() 
                 { 
                  PropA="Customer", 
                  PropB = 1, 
                  PropC = 20, 
                  PropD = 30}, 
              new Test() 
                 { 
                  PropA="Employee", 
                  PropB = 2, 
                  PropC = 5, 
                  PropD = 5}};
    
          var query = testList
                .GroupBy(g => new {g.PropA, g.PropB})
                .Select(group => new {CustomerType = group.Key.PropA,
                          CustomerTypeId = group.Key.PropB,
                          Total1 = group.Sum(C=>C.PropC),
                          Total2 = group.Sum(D=>D.PropD)});
    
          foreach (var item in query)
          {
            Console.WriteLine("{0} ({1}) Total1: {2} Total2: {3}",
                      item.CustomerType,
                      item.CustomerTypeId,
                      item.Total1,
                      item.Total2);
          }
    Hope this helps.
    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    • Proposed as answer by Marcel RomaMVP Thursday, April 29, 2010 8:02 AM
    Thursday, April 29, 2010 6:33 AM
  • Hi Jbassking,

    I fully agree with Deborah.
    If you prefer the query comprehension language, you can also use this snippet:

    var query = from t in testList
          group t by new { t.PropA, t.PropB } into g
          select new { CustomerType = g.Key.PropA, 
            CustomerTypeId = g.Key.PropB,
            Total1 = g.Sum(c => c.PropC),
            Total2 = g.Sum(c => c.PropD)
          };
    

    Marcel

    • Marked as answer by Jbassking Thursday, April 29, 2010 2:04 PM
    Thursday, April 29, 2010 8:02 AM
  • This worked perfect! Thanks.
    Thursday, April 29, 2010 2:04 PM
  • Hi Jbassking,

    You're welcome. But it's really Deborah's answer you should have marked. It is a clear, correct and valid answer to your question. I simply translated it into query comprehension language, that's all.

    Marcel

    Thursday, April 29, 2010 6:26 PM
  • Thanks, Marcel.

    Yea, I am a little bummed because I spent some good time on this one. :-(

    Ah well, no big. :-)


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Friday, April 30, 2010 3:05 PM
  • Thanks DeborahK,  solution is  much more clear.
    Friday, May 11, 2012 11:51 AM