How can I sum values across a generic list using LINQ?

Question

I have this class:

public class ItemsForMonthYear
{
    public String ItemDescription { get; set; }
    public String monthYr { get; set; }
    public int TotalPackages { get; set; }
    public Decimal TotalPurchases { get; set; }
    public Decimal AveragePrice { get; set; }
    public Double PercentOfTotal { get; set; }
}

...into which I'm storing data. I've got this generic list for storing N of them:

List<ItemsForMonthYear> itemsForMonthYearList;

...and then I later read from that list to populate a spreadsheet, which is pretty straightforward:

totPackagesCell.Value2 = ifmy.TotalPackages;
totPurchasesCell.Value2 = ifmy.TotalPurchases;
avgPriceCell.Value2 = ifmy.AveragePrice;

...but the next cell val required is a calculated one based on ALL the TotalPurchases values for a given monthYr. So I need to compute a "percentage of the total" value for all ItemsForMonthYear "records" with the same monthYr value. In SQL I could do something like this:

SELECT SUM(TotalPurchases) FROM ItemsForMonthYear WHERE monthYr = 'Apr 14'

...and then divide that result by the TotalPurchases value for each ItemDescription

IOW, if the sum of all TotalPurchases for the month 'Apr 14' was one million dollars, and TotalPurchases for the ItemDescription "Cocoa Puffs" was one hundred thousand dollars, I could calculate the PercentOfTotal for the "Cocoa Puffs" to be 10%.

I could "brute force it" and loop through the entire generic list:

Decimal allTotalPurchases;
foreach (ItemsForMonthYear ifmy in itemsForMonthYearList)
{
    if (ifmy.monthYr.Equals("Apr 14") 
    {
        allTotalPurchases = allTotalPurchases + ifmy.TotalPurchases;
    }
}

...but I reckon there's a more elegant/performance-friendlier way to accomplish this using LINQ. I want to do it something like this:

percentOfTotalCell.Value2 = GetPercentForPurchaseForMonthYearAsStr(ifmy.TotalPurchases, curMonth);

private String GetPercentForPurchaseForMonthYearAsStr(decimal totPurchasesForItemMonthYear, string totPurchasesForMonthYear)
{    
    percentOfTotal = // What LINQ magic goes here?
    percentOfTotalStr = percentOfTotal.ToString("P", CultureInfo.InvariantCulture);
    return percentOfTotalStr;
}

...where the arg "totPurchasesForItemMonthYear" would be values such as the one hundred thousand dollars from the example above, and the arg "totPurchasesForMonthYear" would be values such as "Apr 14".

Does anyone know a clever LINQ trick of this?

Answer 1

This is Mark Brackett's answer with what I believe are the appropriate fixes:

// This is code that goes outside your loop
var groupedItemsForMonthYr=itemsForMonthYearList.GroupBy(x=>x.monthYr);
var totalsForMonthYr=groupedItemsForMonthYr.ToDictionary(
    x => x.Key, 
    x => x.Sum(y => y.TotalPurchases)
);

// This is the code that goes inside your loop
var percentOfTotal = ifmy.TotalPurchases / totalsForMonthYr[ifmy.monthYr];

Here is my answer given a List<ItemsForMonthYear> and returns a List<ItemsForMonthYear> in items with the PercentOfTotal correctly filled out:

var items=itemsForMonthYearList.GroupBy(x=>x.monthYr)
  .Select(x=>new { total=x.Sum(y=>y.TotalPurchases), i=x })
  .Select(x=>x.i.Select(y=>new ItemsForMonthYear {
        ItemDescription=y.ItemDescription,
        monthYr=y.monthYr,
        TotalPackages=y.TotalPackages,
        TotalPurchases=y.TotalPurchases,
        AveragePrice=y.AveragePrice,
        PercentOfTotal=(double)y.TotalPurchases/(double)x.total
      }))
  .SelectMany(x=>x);

Here's my test code:

void Main()
{
    var itemsForMonthYearList=new[]{
        new ItemsForMonthYear { ItemDescription="A",monthYr="Aug 2014",TotalPackages=1,TotalPurchases=1,AveragePrice=1},
        new ItemsForMonthYear { ItemDescription="A",monthYr="Sep 2014",TotalPackages=1,TotalPurchases=1,AveragePrice=1},
        new ItemsForMonthYear { ItemDescription="A",monthYr="Sep 2014",TotalPackages=1,TotalPurchases=1,AveragePrice=1},
        new ItemsForMonthYear { ItemDescription="A",monthYr="Oct 2014",TotalPackages=1,TotalPurchases=1,AveragePrice=1},
        new ItemsForMonthYear { ItemDescription="A",monthYr="Oct 2014",TotalPackages=1,TotalPurchases=1,AveragePrice=1},
        new ItemsForMonthYear { ItemDescription="A",monthYr="Oct 2014",TotalPackages=1,TotalPurchases=1,AveragePrice=1},
    };

    var items=itemsForMonthYearList.GroupBy(x=>x.monthYr)
      .Select(x=>new { total=x.Sum(y=>y.TotalPurchases), i=x })
      .Select(x=>x.i.Select(y=>new ItemsForMonthYear {
            ItemDescription=y.ItemDescription,
            monthYr=y.monthYr,
            TotalPackages=y.TotalPackages,
            TotalPurchases=y.TotalPurchases,
            AveragePrice=y.AveragePrice,
            PercentOfTotal=(double)y.TotalPurchases/(double)x.total
          }))
      .SelectMany(x=>x);
      items.Dump();
}

public class ItemsForMonthYear
{
    public String ItemDescription { get; set; }
    public String monthYr { get; set; }
    public int TotalPackages { get; set; }
    public Decimal TotalPurchases { get; set; }
    public Decimal AveragePrice { get; set; }
    public Double PercentOfTotal { get; set; }
}

Output:

Answer 2

This code, from Mark Brackett's self-proclaimed "horribly inefficient" first take, works just fine:

private String GetPercentForPurchaseForMonthYearAsStr(decimal totPurchasesForItemMonthYear, string monthToCalculate)
{
    var allRecordsForMonthYr = itemsForMonthYearList.Where(x => x.monthYr == monthToCalculate);
    var totalPurchasesOfMonthYr = allRecordsForMonthYr.Sum(x => x.TotalPurchases);
    var percentOfTotal = totPurchasesForItemMonthYear / totalPurchasesOfMonthYr;
    return percentOfTotal.ToString("P", CultureInfo.CurrentCulture);
}

Since the grouped code wouldn't compile for me, I'm happy with the code above.

Answer 3

the next cell val required is a calculated one based on ALL the TotalPurchases values for a given monthYr. So I need to compute a "percentage of the total" value for all ItemsForMonthYear "records" with the same monthYr value.

Well, first, you need to get the ones with the same monthYr:

var allMonthYr = itemsForMonthYearList.Where(x => x.monthYr == ifmy.monthYr);

Then it's a simple Sum:

var totalPurchasesOfMonthYr = allMonthYr.Sum(x => x.TotalPurchases);
var percentOfTotal = ifmy.TotalPurchases / totalPurchasesOfMonthYr;

Now, that turns out to be horribly inefficient - as you're iterating this list a bunch of times unnecessarily. Much better to group and then sum once:

var groupedItemsForMonthYr = itemsForMonthYearList.ToLookup(x => x.monthYr);
var totalsForMonthYr = groupedItemsForMonthYr.ToDictionary(
    x => x.Key, 
    x => x.Sum(x => x.TotalPurchases)
);

var percentOfTotal = ifmy.TotalPurchases / totalsForMonthYr[ifmy.monthYr];

Answer 4

decimal allTotalPurchases = itemsForMonthYearList.Where(s => s.monthYr.Equals("Apr 14")).Select(s => s.TotalPurchases).Sum();

Answer 5

You can do this to calculate the amount of total purchases on a month:

decimal totalPurchases = itemsForMonthYearList.Where(item => item.monthYr == "Apr 14")
                                              .Sum(item => item.TotalPurchases);

You can then calculate the percentage of a given item by doing:

decimal percentage = (itemsForMonthYearList.Single(item => item.ItemDescription == "Cocoa Puffs")
                                           .TotalPurchases / totalPurchases) * 100;