Q
Problem solve Get help with specific problems with your technologies, process and projects.

COUNT DISTINCT in VB and Access

I am trying to count distinct values in a column using VB6: how many different Player names, in column PlayPlayer,...

are in the table Plays. I am using the following query:

SELECT COUNT(DISTINCT PlayPlayer) FROM Plays

I do not know how to get the count back to my VB program.

You need to assign a column alias to the count:

select count(distinct PlayPlayer) as PlayerCount
  from Plays

Now you can use the PlayerCount column name in VB.

There's one other thing that might cause a problem. Microsoft Access, if that's what you're using, doesn't support COUNT DISTINCT. (At least, it didn't in Access 97. I don't have Access 2000 or 2003, so I can't test it.)

To get around this, just use a saved query, which is the equivalent of a view in Access:

select distinct PlayPlayer
  from Plays

Save the above query as DistinctPlayersQuery. Note that you aren't saving any data, just the SQL. Then your count query becomes:

select count(*) as PlayerCount
  from DistinctPlayersQuery

Since the players are already distinct in the saved query, you can just count them.

For More Information

This was last published in April 2003

Dig Deeper on .NET Development for SQL Server

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

There's one other thing that might cause a problem. Microsoft Access, if that's what you're using, doesn't support COUNT DISTINCT. (At least, it didn't in Access 97. I don't have Access 2000 or 2003, so I can't test it.)

To get around this, just use a saved query, which is the equivalent of a view in Access:

select distinct PlayPlayer
from Plays
Save the above query as DistinctPlayersQuery. Note that you aren't saving any data, just the SQL. Then your count query becomes:

select count(*) as PlayerCount
from DistinctPlayersQuery
Since the players are already distinct in the saved query, you can just count them.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close