Sum up every x values in a row

Question

I have a column calendar week and a column amount. Now I want to sum up the amount for every 4 calendar weeks starting from the first calendar week in April. E.g. if I have 52 (rows) calendar weeks in my initial table I would have 13 (rows) weeks in my final table (I talk about table here since I will try to bind the outcome later to a DGV).

I am using Linq-to-dataset and tried different sources to get a hint how to solve this but group by, aggregate couldnt help me but maybe there are some applications of them that I dont understand so I hope one of you Linq-Experts can help me.

Usually I post code but I can just give you the backbone since I have no starting point.

_dataset = New Dataset1
_adapter.Fill(_dataset.Table1)
dim query = from dt in _dataset.Table1.AsEnumerable()

Answer 1

You can group by anything you like. You could, theoretically, use a simple incrementing number for that:

var groups = dt
    .Select((row, i) => Tuple.Create(row, i / 4))
    .GroupBy(t => t.Item2);

(c# notation)

Then you can calculate the sum for each group:

var sums = groups
   .Select(g => g.Sum(t => t.Item1.Amount));

You mention you want to start at a certain month, e.g. April. You can skip rows by using Skip or Where:

dt.Skip(12).Select(...)

i will always start at 0, making sure your first group contains 4 weeks. However, to know exactly how many weeks to skip or where to start you need more calendar information. I presume you have some fields in your rows that mention the corresponding week's start date:

dt.Where(row => row.StartDate >= firstOfApril).Select(...)