How to get count of unique values in column2 of the datatable

Hi,
I want to get count of unique values in column2 of the datable

1 Like

Use Default View to get Distinct Values,
dataTable.DefaultView.ToTable(true, “column2”);

First argument true returns distinct values of all rows.

3 Likes

Use the same function which will return Distinct values in “Column2” as Data Table, u can use DataTable.rows.count in turn will return ur requirement… :slight_smile:

@Sunitha_Bist Above line will fecth distinct values from the column2 and copy that to new datatable. So you have to store it into a new Datatable.

DataTable uniq_Cols = yourdatatablename.DefaultView.ToTable(true, "column21")

DefaultView.Totable can accept more than one column. The first parameter indicates that the method should fetch distinct values. you can also write yourdatatablename.DefaultView.ToTable(true, “column1”,“column2”); which will fetch distinct values from both the column combination and copy that to new datatable.

Then to get unique results count
int count = uniq_Cols.Rows.count()

Regards…!!
Aksh

6 Likes

else directly

int count=dataTable.DefaultView.ToTable(true, “column2”).rows.count
:slight_smile:

3 Likes

This gives only the distinct column values as a datatable, what if i want to get the complete row of these distinct column values?

Can anyone throw some light on this?

TIA

@Sunitha_Bist

you can also try this

int a = (From p in datatable.Select() select p(“column 2”).ToString).Distinct.ToList().Count

Regards,
Mahesh

@Naveen
Try this
(From p in datatable.Select() select p(“column 2”).ToString).To List

It will give the entire column.

Regards,
Mahesh

Hi @Naveen,

DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);
or
DataTable =DataTable.DefaultView.ToTable(true)

Regards,
Arivu :slight_smile:

Thanks for your reply, but this doesnt help. Say I have 4 columns and 5 rows where column0 has 3 unique values then i need to get 3 rows for those 3 unique values in column0. Tried with what you mentioned but did it didnt help.

can you share your excel file for better understanding @Naveen

Regards,
Arivu :slight_smile:

test.xlsx (12.1 KB)

Please find the attached

Hi @Naveen,

Use this code to get the distinct value based on the column name.

((From LineNo In dt.DefaultView.ToTable(True,"Product").Select().ToList() Select (From row In dt.Select Where row("Product").ToString=LineNo("Product").ToString Select row).ToList(0)).ToList()).CopyToDatatable()

Regards,
Arivu :slight_smile:

1 Like

Thanks Arivu, it works.

@Naveen @arivu96

You can use group by here

(From row in dt.select()
Group row by Product=Convert.Tostring(row.Item(“Program”)) Into Group
Select Group(0)).ToArray.CopyToDataTable()

Regards,
Mahesh

1 Like

Hi @Naveen,

Can you share me some example .xml about this? Im still confused with this.

Thanks