468,270 Members | 1,548 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,270 developers. It's quick & easy.

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


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))",
"ProductionCount > 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
Nov 16 '05 #1
2 54182
Nope, but here's how you do it:

http://support.microsoft.com/default...b;EN-US;326176

For your reference, here's the expression syntax
http://msdn.microsoft.com/library/de...ssionTopic.asp
"Michael Howes" <mh****@xblueiguana.ccom> wrote in message
news:ef**************@TK2MSFTNGP10.phx.gbl...

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))",
"ProductionCount > 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

Nov 16 '05 #2
Michael,
The syntax that ADO.NET supports for Expressions (such as those passed to
Select & Compute) is documented under DataColumn.Expression.

http://msdn.microsoft.com/library/de...ssionTopic.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...
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 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" <mh****@xblueiguana.ccom> wrote in message
news:ef**************@TK2MSFTNGP10.phx.gbl...
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))",
"ProductionCount > 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

Nov 16 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jade | last post: by
reply views Thread by Rakesh | last post: by
7 posts views Thread by Luis Esteban Valencia | last post: by
9 posts views Thread by Bob Achgill | last post: by
5 posts views Thread by Frank | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.