locked
Linq query for distinct group sum RRS feed

  • Question

  • I need to get the sum of the scores for each Category:

    CUSTOMER Category List Score 2015 score 2016
    a T1 1 3
    b T1 1 5
    C T2 3 6
    a T2 5 6
    a T3 6 2
    b T1 7 1

    Result should be sum for each Category:

    Total T1 9 9
    Total T2 8 12
    Total T3 6 2

    How can we do this with Linq query on the DataTable? In actual DataTable i have 3 more years scores.

    Also i want to include scores for blank or null category values.

    Thanks in Advance





    • Edited by wpf_xam1 Monday, October 3, 2016 2:17 AM
    Monday, October 3, 2016 12:54 AM

Answers

  • Hi,

    You can do first a group by the type and then sum the results, like the following. It should work for blank and null values too.

    List<ResultByType> result = dt.AsEnumerable().GroupBy(s => s.Field<string>("Type")).Select(sg => new ResultByType
    {
        Type = sg.First().Field<string>("Type"),
        TotalScore2015 = sg.Sum(s => s.Field<int>("Score2015")),
        TotalScore2016 = sg.Sum(s => s.Field<int>("Score2016"))
    }).ToList();

    Kind regards

    Monday, October 3, 2016 1:56 AM

All replies

  • Hi,

    You can do first a group by the type and then sum the results, like the following. It should work for blank and null values too.

    List<ResultByType> result = dt.AsEnumerable().GroupBy(s => s.Field<string>("Type")).Select(sg => new ResultByType
    {
        Type = sg.First().Field<string>("Type"),
        TotalScore2015 = sg.Sum(s => s.Field<int>("Score2015")),
        TotalScore2016 = sg.Sum(s => s.Field<int>("Score2016"))
    }).ToList();

    Kind regards

    Monday, October 3, 2016 1:56 AM
  • I have modified the question. Please see the updated question
    Monday, October 3, 2016 2:14 AM
  • updated
    Monday, October 3, 2016 2:57 AM
  • I am trying to add the result to the DataTable, this is giving error: specified cast not valid
    Tuesday, October 4, 2016 8:43 AM