Count Distinct in Access: Use a Subquery in FROM

To count distinct in Access, count the rows of a subquery that returns the distinct values. Access SQL does not accept DISTINCT inside COUNT, so the syntax that works in SQL Server fails there.

The Syntax That Works in SQL Server

In SQL Server, the DISTINCT keyword returns each value once. The first query below lists each city name of an Employees table a single time.

SELECT DISTINCT CityName FROM Employees

To count those cities, put DISTINCT inside the COUNT function.

SELECT COUNT(DISTINCT CityName) FROM Employees

This form works in SQL Server and in most other relational databases. DISTINCT also works inside SUM and AVG there. In Access the same syntax fails, and many people struggle with it.

The Subquery That Works in Access

Access SQL has no DISTINCT inside an aggregate function. The usual way round it is a subquery in the FROM clause. The inner query returns the distinct values. The outer query counts its rows.

SELECT COUNT(*) AS CityC
FROM (SELECT DISTINCT CityName FROM Employees) AS CityList;

Give the subquery a name, here CityList, as SQL Server also requires. The count of city names appears in the column CityC. This is the main way to count distinct in Access, and it needs no code beyond SQL.

Think of the query in two steps. The inner query turns a long list of employees into a short list of cities. The outer query counts that short list. The next section builds sample employees, so you can watch both steps with real rows.

The Access queries below follow Access SQL rules and were not run in Access. The SQL Server results are run results.

Watch the NULL Values

The two forms do not treat NULL the same way in SQL Server. COUNT(DISTINCT CityName) ignores NULL. The subquery returns NULL as one more row, and the outer COUNT(*) counts it. The script below builds a small Employees table with one NULL city. It uses a temporary table, so it leaves nothing behind. Run this script in SQL Server Management Studio, because Access has no temporary tables.

CREATE TABLE #Employees (EmployeeID int, CityName nvarchar(30) NULL, StateName nvarchar(30) NULL);
INSERT INTO #Employees (EmployeeID, CityName, StateName)
VALUES (1, N'Austin', N'Texas'), (2, N'Austin', N'Texas'), (3, N'Houston', N'Texas'), (4, N'Denver', N'Colorado'),
       (5, NULL, NULL), (6, N'Portland', N'Oregon'), (7, N'Portland', N'Maine');

SELECT COUNT(DISTINCT CityName) AS DistinctCities FROM #Employees;
SELECT COUNT(*) AS CityC FROM (SELECT DISTINCT CityName FROM #Employees) AS CityList;
SELECT COUNT(*) AS CityC FROM (SELECT DISTINCT CityName FROM #Employees WHERE CityName IS NOT NULL) AS CityList;

The first result is 4, and so is the third, with the filter. The second is 5, because the NULL row counts. In Access the outer COUNT(*) also counts every row it receives. A NULL city in your data adds one to the count. Compare the first and the third query on your own table to see it. To count distinct in Access with the answer of COUNT(DISTINCT), add a filter inside the subquery.

SELECT COUNT(*) AS CityC
FROM (SELECT DISTINCT CityName FROM Employees WHERE CityName IS NOT NULL) AS CityList;

Microsoft documents the same split for the Access Count function. It skips records whose field is Null, unless you pass the asterisk. COUNT(*) counts all records.

Count Distinct Values per Group

How many distinct cities does each state have? In SQL Server, COUNT(DISTINCT CityName) with GROUP BY StateName gives the answer. Access has no COUNT(DISTINCT), so the subquery does the job there. The inner query lists each state and city pair once. The outer query groups by state and counts.

SELECT StateName, COUNT(*) AS CityC
FROM (SELECT DISTINCT StateName, CityName FROM #Employees WHERE CityName IS NOT NULL) AS PlaceList
GROUP BY StateName
ORDER BY StateName;

Texas has two cities, because Austin appears twice and counts once. In SQL Server, the COUNT(DISTINCT) form returns the same four rows.

SELECT StateName, COUNT(DISTINCT CityName) AS CityC
FROM #Employees
WHERE CityName IS NOT NULL
GROUP BY StateName
ORDER BY StateName;

The Access version is the same subquery query. Add ORDER BY StateName at the end to sort it.

SELECT StateName, COUNT(*) AS CityC
FROM (SELECT DISTINCT StateName, CityName FROM Employees WHERE CityName IS NOT NULL) AS PlaceList
GROUP BY StateName;

A Subquery Can Count More Than One Column

You could argue that the subquery is clumsy next to COUNT(DISTINCT). It has one clear advantage. The inner DISTINCT can list several columns, so the outer query counts distinct combinations. The query below counts each city and state pair once.

SELECT COUNT(*) AS PlaceCount
FROM (SELECT DISTINCT CityName, StateName FROM #Employees WHERE CityName IS NOT NULL) AS PlaceList;

The result is 5. Portland, Oregon and Portland, Maine count as two places, and the NULL row is filtered out. The Access form is the same query on the Employees table.

SELECT COUNT(*) AS PlaceCount
FROM (SELECT DISTINCT CityName, StateName FROM Employees WHERE CityName IS NOT NULL) AS PlaceList;

A GROUP BY in the subquery gives the same list of cities as DISTINCT. Some people prefer to write it that way.

SELECT COUNT(*) AS CityC
FROM (SELECT CityName FROM Employees WHERE CityName IS NOT NULL GROUP BY CityName) AS CityList;

Two Saved Queries Work Too

A second approach splits the job. Save the inner query, here as DistinctCities, and count its rows from another query. This avoids a nested query in the SQL view. Some people find that easier to read. The built-in DCount function can also use the saved query as its source.

SELECT DISTINCT CityName FROM Employees;

SELECT COUNT(*) AS CityC FROM DistinctCities;

When you finish, drop the temporary table, or close the window, which removes it.

DROP TABLE #Employees;

Keep one more habit. Name the columns inside the subquery, and avoid SELECT * there. A DISTINCT over every column of a wide table is a different question. It returns every row that differs anywhere. With a unique key, the count equals the number of rows.

What to Remember

To count distinct in Access, put SELECT DISTINCT in a subquery and count its rows with COUNT(*). Filter NULL inside the subquery if you want to match COUNT(DISTINCT) in SQL Server. Name the subquery, and the same code runs in both products. Check the first result against a small list that you count by hand.

A distinct count is not a function in Access, it is a count of a smaller query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.