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

8FD4BED3-0A6A-44CF-8D9F-38CED8AE0***@microsoft.com...
Show quoteHide quote

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

Chris,

You have to modify it a little bit, but this because you have to count
everytime the loop.

http://www.vb-tips.com/default.aspx?ID=dcad9a66-1366-4d61-8d32-1a580eb893b2

I hope this helps,

Cor

Show quoteHide quote

Can't seem to speed up Rows.Add in DataTable
Your kidding me right?
Cannot connect to MSSQL2005 (Local) Server?
Parameterized Query
Transaction Promotion problem