I am trying to count distinct values in a column using VB6: how many different Player names, in column PlayPlayer,...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
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.