LINQ - group by, then sum conditionally

Question

I have a data set (cars):

Brand     DateSold    Amount  Bought/Sold
Toyota    06/07/2015  18.5    Bought
BMW       01/01/2016  25.15   Sold
Mercedes  06/06/2016  20.75   Bought

and I want to group by year and return the sum of the amounts i.e.:

Year Amount  
2015 -18.5   
2016 4.4

and output it into a listbox.

I can sum without the bought/sold condition:

var AmountsByYear = cars.GroupBy(i => i.Date.Year)
                        .Select(g => new { 
                            Year = g.Key,
                            Total = g.Sum(i => i.Amount)
                        }).ToList();
lstAmountsByYear.DataSource = AmountsByYear;

Use the condition inside the `Sum` expression, e.g. something like `g.Sum(i => i.IsSold ? i.Amount : -i.Amount)` — Ivan Stoev, Feb 17 '19 at 10:50

score 1 · Answer 1 · answered Feb 17 '19 at 13:48

Since you haven't given much detail on the how the "Bought/Sold" field is defined in your data structure, I suggest you use an enum. For example,

public class Car
{
    public string Brand;
    public DateTime Date;
    public double Amount;
    public BusinessType BusinessType;
}

public enum BusinessType
{
    Bought = -1,
    Sold = 1
}

This would enable you use your query with minimum change as following to get the expected result.

var AmountsByYear = cars.GroupBy(i => i.Date.Year)
                    .Select(g => new { 
                        Year = g.Key,
                        Total = g.Sum(i => i.Amount*(int)i.BusinessType)
                    }).ToList();

Input Data,

var cars = new List<Car>
{
    new Car{Brand="Toyota", Date = new DateTime(2015,06,07),Amount=18.5,BusinessType=BusinessType.Bought},
    new Car{Brand="BMW", Date = new DateTime(2016,01,01),Amount=25.15,BusinessType=BusinessType.Sold},
    new Car{Brand="Mercedes", Date = new DateTime(2016,06,06),Amount=20.75,BusinessType=BusinessType.Bought},
};

Output

score 0 · Answer 2 · edited May 18 '19 at 11:32

Consider creating an extension method of Car that converts the combination of [Amount, bought/sold] into a positive or negative value for Amount:

public static decimal ToProperAmountValue(this Car car)
{
    return car.IsCarBought ? -car.Amount : car.Amount;
}
// TODO: invent proper method name

Furthermore, use the correct overload of Enumerable.GroupBy that already does the Select for you:

var amountsByYear = cars.GroupBy(

    // KeySelector: Group Cars into Groups of same year
    car => car.Date.Year)

    // ResultSelector: take the Key and the Cars with this Key to make a new object
    (year, carsInThisYear => new
    { 
        Year = year,
        Total = carsInThisYear.Sum(car => car.ToProperAmountValue())
    })
    .ToList();

LINQ - group by, then sum conditionally

2 Answers2