CODEWITHSUNDEEP
c#sqloracle-databaselinq
smuldr
smuldr

Reputation: 324

Count items in list with LINQ

I have a list and each item on the list has an ID, Name, and Rank. Is there a way to count how many times a name has a rank of 1-7 and organize so that Name shows and how many times it appears in each rank?

Sample Data:
ID          NAME          RANK
123         ENGL 103      1
456         CPSC 105      1
123         ENGL 103      2
789         CHEM 120      4

I am getting the above data with a simple SQL query. If perhaps this could be done better in SQL I am ok with that as well.

Expected result:
NAME     RANK1  RANK2  RANK3  RANK4  RANK5  RANK6  RANK7  
ENGL 103  1       1     0       0     0      0      0
CPSC 105  1       0     0       0     0      0      0
CHEM 120  0       0     0       1     0      0      0

Upvotes: 0

Views: 261

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

In Oracle it is simple pivot:

select * 
  from (select name, rank from tbl)
  pivot (count(1) for rank in (1 r1, 2 r2, 3 r3, 4 r4, 5 r5, 6 r6, 7 r7))

dbfiddle demo

Upvotes: 0

Péter
Péter

Reputation: 322

list
   .Where(item => item.Rank >= 1 && item.Rank <= 7)
   .GroupBy(item => item.Name)
   .Select(item => new { Name = item.Key, Count = item.Count() });

or

list
   .Where(item => item.Rank >= 1 && item.Rank <= 7)
   .GroupBy(item => item.Name)
   .Select(item => new { 
        Name = item.Key, 
        Rank1 = item.Count(p => p.Rank == 1),
        Rank2 = item.Count(p => p.Rank == 2),
        Rank3 = item.Count(p => p.Rank == 3),
        Rank4 = item.Count(p => p.Rank == 4),
        Rank5 = item.Count(p => p.Rank == 5),
        Rank6 = item.Count(p => p.Rank == 6),
        Rank7 = item.Count(p => p.Rank == 7),
   });

Upvotes: 0

juharr
juharr

Reputation: 32266

You can get those result with the following Linq

var results = data
    .GroupBy(x => x.Name)
    .Select(grp => new
    {
        Name = grp.Key,
        Rank1 = grp.Count(x => x.Rank == 1),
        Rank2 = grp.Count(x => x.Rank == 2),
        Rank3 = grp.Count(x => x.Rank == 3),
        Rank4 = grp.Count(x => x.Rank == 4),
        Rank5 = grp.Count(x => x.Rank == 5),
        Rank6 = grp.Count(x => x.Rank == 6),
        Rank7 = grp.Count(x => x.Rank == 7)
    })
    .ToList();

Once you group the data on the Name you can then count how many in each group has each of the Rank values you are interested in.

Upvotes: 1

Related Questions