Search code examples
sqlms-access

SELECT Count Distinct Syntax MS Access SQL

Table: Test

Customer Number
5
6
7
8
8  



SELECT COUNT(DISTINCT[Customer Number])
FROM Test

Expected Result = 4

Solution

  • MS Access does not support COUNT(DISTINCT). You can use two aggregations:

    select count(*)
from (select distinct [Customer Number]
      from Test 
     ) as t;

    Note: This counts NULL values whereas COUNT(DISTINCT) does not. You can filter them out in either the subquery or outer query if that is an issue.