Geek Answers Handbook

Select Count (Distinct Value) returns 1

I am developing a query in SSMS 2005 that looks something like this:

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

When I run a query using COUNT (), it returns the value 1. When I run it without COUNT (), SSMS reports the correct value, for example, 212 entries.

This column refers to the data type (16, 0).

For those who may ask, full request:

 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' )

This query basically provides a recurring business metric between two 12-month periods.

So, I wonder why “COUNT (DISTINCT ColumnName)” returns 1 when “ColumnName IS NOT NULL” is indicated?

Here is sample data when SELECT TOP 10 DISTINCT ColumnName FROM ... is executed, etc.

 1346116 1346131 1346425 1346923 1349935 1350115 1350153 2594787 2821944 2879631
+7
sql sql-server count distinct
source share
5 answers

Using the number (16, 0) made me suspect that it was a data type. Add CAST to the COUNT clause to apply it to the INT type:

 Count(Distinct Cast(O_ID as Int))
+5
source share

I assume that all rows returned the same value for O_ID . You can do COUNT(*) or COUNT() for a key that is unique for each row to get the number of rows.

0
source share

Delete DISTINCT and you will get a score for all rows.

0
source share

Could you run these queries:

 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 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' )

and

 SELECT DISTINCT TOP 5 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 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' ) ORDER BY O_ID

verbatim without changing anything?

0
source share
 SELECT COUNT(*) 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' ) GROUP BY O_Id
0
source share

More articles:


All Articles