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.

MS Access - Count Distinct Values CountDistinctValues-800x343

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.

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

, , ,
Previous Post
SQL SERVER – Identity Column is Difficult to Remove
Next Post
SQL SERVER – COUNT(*) and Index Used – Part 2

Related Posts

Leave a Reply

Menu