选择计数（特殊值）返回1 [英] Select Count(Distinct Value) returns 1

问题描述

我在 SSMS 2005中设计一个查询，看起来像这样：

I'm designing a query in SSMS 2005 which looks something like this:

SELECT COUNT(DISTINCT ColumnName) FROM Table WHERE ColumnName IS NOT NULL

当我使用COUNT（）运行查询时，它返回值1.当我运行它没有COUNT（），SSMS报告正确的值，例如212记录。

When I run the query with COUNT() it returns the value 1. When I run it without COUNT(), SSMS reports the correct value eg 212 records.

有问题的列的数据类型为numeric（16，0）。

The column in question is of datatype numeric(16, 0).

对于可能请求的用户， full是：

For those who might ask, the query in full is:

SELECT COUNT(DISTINCT O_ID) FROM vEmployers
INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer
WHERE E_START >= '01-AUG-2008' AND E_START < '01-AUG-2009'
AND O_ID IS NOT NULL AND O_ID IN (
    SELECT O_ID FROM vEmployers
    INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer
    WHERE E_Start < '01-AUG-2008' and E_Start >= '01-AUG-2007'
)

所以我想知道为什么COUNT（DISTINCT ColumnName） 当指定ColumnName is NOT NULL时返回1？

So I'm wondering why "COUNT(DISTINCT ColumnName)" is returning 1 when "ColumnName IS NOT NULL" has been specified?

这里是SELECT TOP 10 DISTINCT ColumnName FROM .. 。etc运行：

Here is a sample of the data when SELECT TOP 10 DISTINCT ColumnName FROM... etc is run:

1346116
1346131
1346425
1346923
1349935
1350115
1350153
2594787
2821944
2879631

推荐答案

使用数字（16，0）使我怀疑它是数据类型相关的。在COUNT子句中添加一个CAST以将其转换为INT类型：

The use of the numeric(16, 0) made me suspect that it was data type related. Add a CAST in the COUNT clause to cast it to an INT type:

Count(Distinct Cast(O_ID as Int))

这篇关于选择计数（特殊值）返回1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！