Search code examples
sqlms-access

how do I do a count distinct group by in ms access?

SELECT [dataset].[ID], count(distinct([dataset].dates)) AS distinct_dates
FROM [dataset]
GROUP BY [dataset].[ID];

this gives the error UnDefined function Distinct in expression

Solution

  • You can use two levels of aggregation:

    SELECT [dataset].[ID], COUNT(*) AS distinct_dates
FROM (SELECT DISTINCT id, dates
      FROM [dataset]
     ) as d
GROUP BY [dataset].[ID];

    Strictly speaking this is not 100% the same, because this would count NULL as a separate value (but count(distinct) ignores NULL values). So, to be identical, you would need to use:

    SELECT [dataset].[ID], COUNT(dates) AS distinct_dates