LINQ with grouping and summation

Question

I need to group on dept and needWeek and then add sum together, add open together, and add closed together.

This query:

var query8 = from q8 in query7
             group q8 by new { q8.q7.dept, q8.needWeek, q8.q7.allCount, q8.q7.openCount, q8.q7.closedCount } into g
             select new
             {
                 dept = g.Key.dept,
                 needWeek = g.Key.needWeek,
                 sum = g.Sum(q8 => g.Key.allCount),
                 open = g.Sum(q8 => g.Key.openCount),
                 closed = g.Sum(q8 => g.Key.closedCount)
             };

will return:

{ dept = foo, needWeek = 05/20/12, sum = 7, open = 3, closed = 4 }
{ dept = foo, needWeek = 05/20/12, sum = 2, open = 0, closed = 2 }

but I need:

{ dept = foo, needWeek = 05/20/12, sum = 9, open = 3, closed = 6 }

How close am I?

Answer 1

You are close, but you're grouping by all unnecessary fields. Just group by what is common to all.

var query8 =
    from q8 in query7
    group new { q8.q7.allCount, q8.q7.openCount, q8.q7.closedCount }
       by new { q8.q7.dept, q8.needWeek }
       into g
    select new
    {
        g.Key.dept,
        g.Key.needWeek,
        sum = g.Sum(x => x.allCount),
        open = g.Sum(x => x.openCount),
        closed = g.Sum(x => x.closedCount),
    };

Answer 2

I need to group on dept and needWeek

So why doesn't your code follow your needs? Why are you grouping by everything?

group q8 by new { q8.q7.dept, q8.needWeek } into g
select new
{
    dept = g.Key.dept,
    needWeek = g.Key.needWeek,
    sum = g.Sum(q8 => g.q7.allCount),
    open = g.Sum(q8 => g.q7.openCount),
    closed = g.Sum(q8 => g.q7.closedCount)
};