Find Unique Values in a DataTable Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a datatable with 330 rows of data. ColumnA has repeating values. I
would like to count the number of unique items in ColumnA. I looked into
using a dataview, but I'm not sure what to use for the rowfilter string.

Does anyone know how I can do this.

Thanks,
Chris
 
the filter string looks like a WHERE clause of a Sql Query (ex: CustomerID =
1)

If you want to get all items that occurs only once, I suggest you to use
this code snippet :

private string GetFilter()
{
// I suppose column 1 is a "int32" datatype in the source. I'll store
foreach column the number of time it appears
Dictionnary<int, int> occurenceCounter = new Dictionnary<int, int>();

// I suppose my BindingSource is linked to a strongly typed DataSet
foreach(MyDS.OrderRow cRow in this._globalDataSet.Orders)
{
if(occurenceCounter.ContainsKey(cRow.CustomerID))
{
occurenceCounter.Add(cRow.CustomerID, 1); // First time it appears
}
else
{
occurenceCounter[CustomerID]++; // increment the counter
}
}

// Let's construct the filter
StringBuilder sb = new StringBuilder();

if(occurenceCounter.Count !=0)
{
foreach(int customerID in occurenceCounter.Keys)
{
if(occurenceCounter[customerID] == 1)
{
// It appears only once, add it to the filter :
sb.AppendFormat(
CultureInfo.InvariantCulture,
"CustomerID = '{0}' OR ", // Do not forget the space between OR and the "
customerID
);
}

// Remove the last OR
sb.Remove(sb.Length -4, 4);

// Sets the filter with something like CustomerID = '1' OR CustomerID =
'65' etc...
myBindingSource.RowFilter = sb.ToString();

}


}

Hope that helps

Steve
 
Depends on what db you're using, but generally you can use the
following SQL statements

SELECT count ( columnA ) FROM tblName GROUP BY columnA

Oracle supports SELECT count distinct ( columnA ) FROM tblName

Also

SELECT distinct ( columnA ) FROM tblName

does not count but returns unique values
 
Back
Top