CODEWITHSUNDEEP
sqlms-access
user15851574
user15851574

Reputation:

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

Upvotes: 0

Views: 174

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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

Upvotes: 1

Related Questions