LINQ to SQL: orderby a sum operation

Question

I have a table where I need order a result set by sum a numeric column according to a group by clause.

For example, the table has a salesperson ID, order number, and order value. I want a top 10 list of my top salespeople by accumulated order value.

New to SQL and LINQ, so any advice would be appreciated!

Bala R · Answer 1 · 2011-06-12T01:40:13.947

7

var results = context.Sales.GroupBy(s => s.SalesPersonID)
                           .Select(g => new {
                                              SalesPersonID = g.Key, 
                                              Sales = g.Sum(s => s.OrderValue) 
                                             })
                           .OrderByDescending(r => r.Sales)
                           .Take(10);

edited Jun 12 '11 at 01:40

answered Jun 12 '11 at 00:15

Bala R

107,317
23
199
210

score 2 · Answer 2 · answered Jun 12 '11 at 00:16

Something like this:

var orders = new[]
{
    new Order { SalesPersonId = 1, OrderNumber = 1, OrderValue = 1 },
    new Order { SalesPersonId = 1, OrderNumber = 2, OrderValue = 2 },
    new Order { SalesPersonId = 2, OrderNumber = 2, OrderValue = 2 },
};

var topSalesMen = orders
    .GroupBy(arg => arg.SalesPersonId)
    .Select(arg => new { SalesPersonId = arg.Key, TotalOrderValue = arg.Sum(x => x.OrderValue) })
    .OrderByDescending(arg => arg.TotalOrderValue)
    .Take(10)
    .ToList();

LINQ to SQL: orderby a sum operation

2 Answers2