GROUPBY and SUM on List items using LINQ

Question

I have a List of type DailySummary

public class DailySummary
{
    public string AffiliateID { get; set; }
    public string TotalCalls { get; set; }
    public string Date{ get; set; }
}

with following sample data:

List<DailySummary> DealerTFNDatesTable = new List<DailySummary>();
DealerTFNDatesTable.Add(new DailySummary() { AffiliateID="0", Date = "12/12/2016", TotalCalls = "10"});
DealerTFNDatesTable.Add(new DailySummary() { AffiliateID="0", Date = "12/13/2016", TotalCalls = "74"});
DealerTFNDatesTable.Add(new DailySummary() { AffiliateID="1", Date = "12/22/2016", TotalCalls = "63"});
DealerTFNDatesTable.Add(new DailySummary() { AffiliateID="0", Date = "12/12/2016", TotalCalls = "58"});

Now I want to retrieve Date and TotalCalls grouped by AffiliateID and assign in another list.

for(int i =0; i < DealerTFNDatesTable.Count; i++)
{
    List<NewList> newList = new List<NewList>();
    newList.Date = //Assign Dintinct dates WHERE AffiliateId = 0 
    newList.AffiliateID = //AffiliateID=0
    newList.TotalCalls= //TotalCalls SUM GROUPBY DATE and AffiliateID = 0 
                        //For Date '12/12/2016' it will be 68, For '12/13/2016' it will be 74 and so on
}

I'm sorry, I'm new to LINQ. Can someone help me or share resources where I can get a hint to achieve this?

Staeff · Accepted Answer · 2016-12-27T10:54:07.113

This should work for grouping by AffilateID and Date and then getting the sum (though it's weird to store a number as a string for something like this, but whatever floats your boat).

 var results = DealerTFNDatesTable
     .GroupBy(x => new { x.AffiliateID, x.Date })
     .Select(x => new DailySummary {
        AffiliateID = x.First().AffiliateID,
        Date = x.First().Date,
        TotalCalls = x.Sum(y => Convert.ToInt32(y.TotalCalls)).ToString()
     });

If you now look at the result, for example with this code, you get exactly the values you wanted:

foreach (var x in results) {
    Console.WriteLine($"id = {x.AffiliateID}, date = {x.Date}, totalCalls = {x.TotalCalls}");
}

> id = 0, date = 12/12/2016, totalCalls = 68
> id = 0, date = 12/13/2016, totalCalls = 74
> id = 1, date = 12/22/2016, totalCalls = 63

Perfect and simple solution. Thanks a lot :) – Fact Finder Dec 27 '16 at 11:31 — Fact Finder, Dec 27 '16 at 11:31

score 1 · Answer 2 · answered Dec 27 '16 at 10:47

First off,

Since DealerTFNDatesTable is a variable, you should use camel case. Thus it is dealerTFNDatesTable

Then to complete @andy his answer, as you also want to do a select. You can select it as follows:

        var newVariable = from item in dealerTFNDatesTable
                   group item by new
                   {
                       item.Date,
                       item.AffiliateID,
                   }
                   into g
                   select new
                   {
                       Date = g.Key.Date,
                       Id = g.Key.AffiliateID,
                       Total = g.Sum(a => a.TotalCalls)
                   };

This will give you an IEnumerable, of which you can put the relevant parts in a list by doing var otherList = new List<object>(newVariable .Where(a => a.Total > 0)); or simply add .ToList() after the select if you want the collection as-is.

Note that this is simply another notation than LINQ, the result is the same.

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

var results = DealerTFNDatesTable.GroupBy(T => new { T.AffiliateID })

Link

edited May 23 '17 at 11:53

Community

1
1

answered Dec 27 '16 at 09:41

andy

5,979
2
27
49

1

While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! – kayess May 23 '17 at 13:31

GROUPBY and SUM on List items using LINQ

3 Answers3