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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
