Can DataTable's Select or Compute do Count(Distinct())

[Michael Howes]

I have a single DataTable in a DataSet. It has 4 columns and i'd like to
get a handful of counts of unique items in 3 of the 4 columns.
Can a DataTables Select or Compute methods to COUNT DISTINCT?

These two attempts failed
DataRow[] dr = ds.Tables[0].Select( "COUNT(DISTINCT (site_name))" );

object x = ds.Tables[0].Compute( "COUNT(DISTINCT (site_name))",
"ProductionCoun t > 0" );

The filter in the Compute I don't really want because I'd like to count
distinct on all rows..but the method forces me ot have a filter expression

thanks
mike

[William Ryan eMVP]

Re: Can DataTable's Select or Compute do Count(Distinct( ))

Nope, but here's how you do it:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;326176

For your reference, here's the expression syntax

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.asp

"Michael Howes" <mhowes@xblueig uana.ccom> wrote in message
news:efannU7MEH A.2704@TK2MSFTN GP10.phx.gbl...[color=blue]
>
> I have a single DataTable in a DataSet. It has 4 columns and i'd like to
> get a handful of counts of unique items in 3 of the 4 columns.
> Can a DataTables Select or Compute methods to COUNT DISTINCT?
>
> These two attempts failed
> DataRow[] dr = ds.Tables[0].Select( "COUNT(DISTINCT (site_name))" );
>
> object x = ds.Tables[0].Compute( "COUNT(DISTINCT (site_name))",
> "ProductionCoun t > 0" );
>
> The filter in the Compute I don't really want because I'd like to count
> distinct on all rows..but the method forces me ot have a filter expression
>
> thanks
> mike
>
>[/color]

[Jay B. Harlow [MVP - Outlook]]

Re: Can DataTable's Select or Compute do Count(Distinct( ))

Michael,
The syntax that ADO.NET supports for Expressions (such as those passed to
Select & Compute) is documented under DataColumn.Expr ession.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.asp

Unfortunately Distinct is not one of the supported functions.

What I've done is create a second table that has primary keys that match the
columns that I want to count, plus a count column. Then for each row in my
primary table I add or update the count in this second table. I use
DataTable.Rows. Find to find the matching row...
[color=blue]
> The filter in the Compute I don't really want because I'd like to count
> distinct on all rows..but the method forces me ot have a filter expression[/color]
You can pass null for the filter to have it process all rows.

For a good tutorial on ADO.NET as well as a good desk reference once you
know ADO.NET see David Sceppa's book "Microsoft ADO.NET - Core Reference"
from MS press

Hope this helps
Jay


"Michael Howes" <mhowes@xblueig uana.ccom> wrote in message
news:efannU7MEH A.2704@TK2MSFTN GP10.phx.gbl...[color=blue]
>
> I have a single DataTable in a DataSet. It has 4 columns and i'd like to
> get a handful of counts of unique items in 3 of the 4 columns.
> Can a DataTables Select or Compute methods to COUNT DISTINCT?
>
> These two attempts failed
> DataRow[] dr = ds.Tables[0].Select( "COUNT(DISTINCT (site_name))" );
>
> object x = ds.Tables[0].Compute( "COUNT(DISTINCT (site_name))",
> "ProductionCoun t > 0" );
>
> The filter in the Compute I don't really want because I'd like to count
> distinct on all rows..but the method forces me ot have a filter expression
>
> thanks
> mike
>
>[/color]