# How to calculate total using linq

I have the result of a linq query as shown in the picture. In the last request (not shown) I am grouping Year by LeaveType. However, I want to calculate the running grand total for leaveCarriedOver for each type over several years. That is, the sick LeaveCarriedOver in 2010 becomes the "opening" balance for sick leave in 2011 plus one for 2011. I made another query on the shown list of results that looks like this:

``````            var leaveDetails1 = (from l in leaveDetails
select new
{
l.Year,
l.LeaveType,
l.LeaveTaken,
l.LeaveAllocation,
l.LeaveCarriedOver,
RunningTotal = leaveDetails.Where(x => x.LeaveType == l.LeaveType).Sum(x => x.LeaveCarriedOver)
});
```

```

where leaveDetails is the result of the image.

The resulting RunningTotal is not cumulative as expected. How can I achieve my original goal. Open to any ideas - my last option would be to do this in javascript in the frontend. thanks in advance

+3

source to share

A simple implementation is to first get a list of possible totals and then get a sum from the details for each of these categories.

get a separate list `Year`

and `LeaveType`

- this is a group by and select the first of each group. we return a `List<Tuple<int, string>>`

, where `Int`

is the year, and `string`

is the LeaveType value

`````` var distinctList = leaveDetails1.GroupBy(data => new Tuple<int, string>(data.Year, data.LeaveType)).Select(data => data.FirstOrDefault()).ToList();
```

```

then we want to sum for each of these elements was necessary, so you want to choose this list to return the id ( `Year`

and `LeaveType`

) plus the total value, so the added value for `Tuple<int, string, int>`

.

``````var totals = distinctList.Select(data => new Tuple<int, string, int>(data.Year, data.LeaveType, leaveDetails1.Where(detail => detail.Year == data.Year && detail.LeaveType == data.LeaveType).Sum(detail => detail.LeaveCarriedOver))).ToList();
```

```

after reading the line above, you can see that it accepts the individual totals we want to list, create a new object, save `Year`

and `LeaveType`

for reference, and then set the latter `Int`

using `Sum<>`

filtered details for `Year`

and `LeaveType`

.

0

source

If I fully understand what you are trying to do, I don't think I will rely solely on the built-in LINQ operators. I think (emphasis on thought) that any combination of built-in LINQ operators is going to solve this problem in O (n ^ 2) runtime.

If I was going to implement this in LINQ, I would create an extension method for IEnumerable that is similar to a function `Scan`

in reactive extensions (or find a library in there that already implemented it):

``````public static class EnumerableExtensions
{
public static IEnumerable<TAccumulate> Scan<TSource, TAccumulate>(
this IEnumerable<TSource> source,
TAccumulate seed,
Func<TAccumulate, TSource, TAccumulate> accumulator)
{
// Validation omitted for clarity.
foreach(TSource value in source)
{
seed = accumulator.Invoke(seed, value);
yield return seed;
}
}
}
```

```

Then it should be done around O (n log n) (due to order of operations):

``````leaveDetails
.OrderBy(x => x.LeaveType)
.ThenBy(x => x.Year)
.Scan(new {
Year = 0,
LeaveType = "Seed",
LeaveTaken = 0,
LeaveAllocation = 0.0,
LeaveCarriedOver = 0.0,
RunningTotal = 0.0
},
(acc, x) => new {
x.Year,
x.LeaveType,
x.LeaveTaken,
x.LeaveAllocation,
x.LeaveCarriedOver,
RunningTotal = x.LeaveCarriedOver + (acc.LeaveType != x.LeaveType ? 0 : acc.RunningTotal)
});
```

```

You are not saying, but I am assuming the data comes from a database; if so, then you can get `leaveDetails`

back already sorted and skip sorting here. This will take you to O (n).

If you don't want to create an extension method (or find one) then it will do the same (just in an ugly way).

``````var temp = new
{
Year = 0,
LeaveType = "Who Cares",
LeaveTaken = 3,
LeaveAllocation = 0.0,
LeaveCarriedOver = 0.0,
RunningTotal = 0.0
};
var runningTotals = (new[] { temp }).ToList();
runningTotals.RemoveAt(0);

foreach(var l in leaveDetails.OrderBy(x => x.LeaveType).ThenBy(x => x.Year))
{
var s = runningTotals.LastOrDefault();
{
l.Year,
l.LeaveType,
l.LeaveTaken,
l.LeaveAllocation,
l.LeaveCarriedOver,
RunningTotal = l.LeaveCarriedOver + (s == null || s.LeaveType != l.LeaveType ? 0 : s.RunningTotal)
});
}
```

```

It should also be O (n log n) or O (n) if you can pre-sort `leaveDetails`

.

0

source

If I understand the question, you want something like

``````decimal RunningTotal = 0;

var results = leaveDetails
.GroupBy(r=>r.LeaveType)
.Select(r=> new
{
Dummy = RunningTotal = 0 ,
results = r.OrderBy(o=>o.Year)
.Select(l => new
{
l.Year,
l.LeaveType ,
l.LeaveAllocation,
l.LeaveCarriedOver,
RunningTotal = (RunningTotal = RunningTotal + l.LeaveCarriedOver )
})
})
.SelectMany(a=>a.results).ToList();
```

```

Mostly overload is used `Select<TSource, TResult>`

to calculate the current balance, but grouped by LeaveType first, so we can reset RunningTotal for each LeaveType and then ungroup at the end.

0

source

All Articles