CODEWITHSUNDEEP
c#asp.netdatatable
mFine
mFine

Reputation: 3

How to return count of distinct and repeated values in a datatable?

I have an excel sheet I upload. It has columns for the usernames and which server they are registered for. I want to count the number of rows of each unique username on each of the servers.

Table:
Username | Server
bob        Production0
bob        Production1
mike       Production1
Jerry      Production2
Tom        Production0

Output:
On Production0 = 2 Unique Users (Bob and Tom)
Production1 = 2 Unique Users (Bob and Mike)
Production2 = 1 Unique Users (Jerry)

So for Production0 for example, I want to figure out how to make the Username get unique names but keep the name if its on a different server as well:

DataView newView = new DataView(uploadedfileDataTable);
//Get only the username and Server columns     
DataTable wantedTable = newView.ToTable(false,"Column A","Column B"); //Get only the username and Server columns
     int prodCount = wantedTable.AsEnumerable().Count(row => row.Field<string>("Column A") ==  "//HOW TO MAKE DISTINCT" && row.Field<string>("Column B") == "Production0");
     numUsersProd0.Text = prodCount.ToString();

Upvotes: 0

Views: 4569

Answers (1)

juharr
juharr

Reputation: 32286

Just group by the server name then do a count of the Distinct users.

var results = watedTable.AsEnumerable()
    .GroupBy(row => row.Field<string>("Column B"))
    .Select(grp => new 
    { 
        Server = grp.Key, 
        UserCount = grp.Select(row => row.Field<string>("ColumnA")).Distinct().Count()
    });

foreach(var r in results)
    Console.WriteLine(r.Server + " has " + r.UserCount + " users.");

Or if you know the server you want the count for then filter on that name, select the user name, and count the distinct values.

var prod0Count = watedTable.AsEnumerable()
    .Where(row => row.Field<string>("Column B") == "Production0")
    .Select(row => row.Field<string>("ColumnA"))
    .Distinct()
    .Count();

Upvotes: 1

Related Questions