Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select the distinct row count of a column in a data table?

I have a data table:

DataTable table = new DataTable();

DataColumn column;

column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "RelationshipTypeDescription";
table.Columns.Add(column);

column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "RelatedContactName";
table.Columns.Add(column);

I want to know the DISTINCT COUNT OF COLUMN "RelationshipTypeDescription".

I'm not sure how to refer to the column name in this:

int relationshipCount = table.AsEnumerable().Distinct().Count();

Can someone give me a hand?

like image 687
JJ. Avatar asked Aug 14 '13 21:08

JJ.


People also ask

How do you find the number of unique rows from a table?

To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.

How do I select distinct and count in SQL?

The correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.

Can we use distinct with count (*)?

Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table.


2 Answers

You can do this:

int relationshipCount = table
    .AsEnumerable()
    .Select(r => r.Field<string>("RelationshipTypeDescription"))
    .Distinct()
    .Count();

But you probably don't need to call AsEnumerable:

int relationshipCount = table
    .Select(r => r.Field<string>("RelationshipTypeDescription"))  // Compiler error: "Cannot convert lambda expression to type 'string' because it is not a delegate type"
    .Distinct()
    .Count();
like image 109
p.s.w.g Avatar answered Oct 07 '22 23:10

p.s.w.g


You can also create a new datatable containing only the distinct values of your table:

DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "RelationshipTypeDescription");

More info: https://stackoverflow.com/a/1199956/1822214

http://msdn.microsoft.com/en-us/library/wec2b2e6.aspx

like image 6
Forest Kunecke Avatar answered Oct 07 '22 23:10

Forest Kunecke