SELECT [dataset].[ID], count(distinct([dataset].dates)) AS distinct_dates
FROM [dataset]
GROUP BY [dataset].[ID];
这给出了错误UnDefined function Distinct in expression
您可以使用两个级别的聚合:
SELECT [dataset].[ID], COUNT(*) AS distinct_dates
FROM (SELECT DISTINCT id, dates
FROM [dataset]
) as d
GROUP BY [dataset].[ID];
严格地说,这不是100%相同的,因为这将把NULL
作为一个单独的值来计数(但count(distinct)
忽略NULL
的值(。因此,为了完全相同,您需要使用:
SELECT [dataset].[ID], COUNT(dates) AS distinct_dates