MS Access – Count Distinct Values

I love answering random questions and particularly when they are from my paying clients of Comprehensive Database Performance Health Check. Quite often I have learned more from the random questions than any other sources. Let me answer today a very simple question about MS Access and Count Distinct Values.

In SQL Server we have DISTINCT Keyword which displays only unique values of columns used in the SELECT statement. For example, if you want to want to display a unique city name from the employees’ table, you can run the following query.

[SQL]SELECT DISTINCT CityName FROM Employees[/sql]

Now if you want to count how many DISTINCT CityNames are there you can run the following query.

[SQL]SELECT COUNT(DISTINCT CityName) FROM Employees[/sql]

Well, here we are counting but you can also use other appropriate aggregated functions as well. The syntax will work just fine with other relational databases. However, it will not work as it is with MS Access. Lots of people in MS Access struggle to use this syntax.

Here is the syntax for MS Acess where you can Count Distinct Values.

[SQL]SELECT COUNT(*) AS CityC FROM
(SELECT DISTINCT CityName FROM Employees)[/sql]

It is that simple syntax.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171
• Best Value for Maximum Worker Threads – SQL in Sixty Seconds #170
• Copy Database – SQL in Sixty Seconds #169

Reference: Pinal Dave (http://blog.SQLAuthority.com)