Select grouped result.

Question

I have a list of clients

public class Client
{
    public string ClientID { get; set; } // Client
    public decimal Amount { get; set; } // Sales $
}

eg. with the following data

ClientID | Amount
---------+-------
000021354|   200
000021353|   300
000021353|   400
000021352|   100
000021351|   200
000021350|   100

the code:

List<Client> cList = new List<Client>();
cList.Add(new Client() { ClientID = "000021354", Amount = 200 });
cList.Add(new Client() { ClientID = "000021353", Amount = 300 });
cList.Add(new Client() { ClientID = "000021353", Amount = 400 });
cList.Add(new Client() { ClientID = "000021352", Amount = 100 });
cList.Add(new Client() { ClientID = "000021351", Amount = 200 });
cList.Add(new Client() { ClientID = "000021350", Amount = 100 });

I want it to be grouped by ClientID and Sumup by sales. Choose the top 3 (customers with most sales) - the rest should be grouped into "others"

Thus, the result should be:

ClientID | Amount
---------+-------
000021353|   700 #1 (300 + 400)
000021354|   200 #2
000021351|   200 #3
others   |   200 // (000021352 + 000021350)

But for some reason my grouping is not working:

var Grouped = cList.GroupBy(x => x.ClientID)
                .OrderByDescending(x => x.Select( y=> y.Amount).Sum())
                .Select(x => x).Take(3); //how to add "others" ?

Answer 1

I would go with something like this

var grouped = a.GroupBy(x => x.ClientID, y => y.Amount)
               .Select(x => new Client { ClientID = x.Key, Amount = x.Sum() })
               .OrderByDescending(x => x.Amount);

var final = grouped.Take(3).ToList();
final.Add(new Client { ClientID = "Others", Amount = grouped.Skip(3).Sum(x => x.Amount) });

Answer 2

var grouped = cList.GroupBy(x => x.ClientID)
                   .Select(g => new { Id = g.Key, TotalAmount = g.Sum(c => c.Amount)})
                   .OrderByDescending(x => x.TotalAmount);

var result = grouped.Take(3).ToList();
result.Add(new {Id = "others", TotalAmount = grouped.Skip(3).Sum(x => x.TotalAmount)});

Answer 3

, , , , , .

var query=cList.GroupBy(x => x.ClientID)
               .Select(g=>new{UserId=g.Key, Amount=g.Sum(y=>y.Amount)})
               .OrderByDescending(e=>e.Amount);

var result= query.Take(3).Concat(new[]{new {UserId="others", Amount=query.Skip(3).Sum(e=>e.Amount)}});

PS: , , linq , , , Client

Answer 4

:

        var Grouped = (from Group in (from client in clients
                                      group client by client.Id into clientGroup
                                      select new { Id = clientGroup.Key, Sum = clientGroup.Sum(cc => cc.Amount) })
                       orderby Group.Sum descending
                       select Group).Take(3).ToList(); ;
        var Top3 = Grouped.Take(3).ToList();
        var Others = Grouped.Skip(3).ToList();

, "" . :

var Others = Grouped.Skip(3).Sum(g => g.Sum);

.

Answer 5

Remember to GroupByreturn a group, not a list Client. You can easily project the group back into the list, in this case using an anonymous object.

var clients = cList.GroupBy(x => x.ClientID)
                     .Select(x => new { ClientID = x.Key, AmountTotal = x.Sum(c => c.Amount) })
                     .OrderByDescending(x => x.AmountTotal);

var topThree = clients.Take(3);
var others = clients.Except(topThree);