Higher Query result with the DISTINCT Keyword?

Say I have a table with 100,000 User IDs (UserID is an int). When I run a query like

SELECT COUNT(Distinct User ID) from tableUserID

the result I get is HIGHER than the result from the following statement:

SELECT COUNT(User ID) from tableUserID

I thought Distinct implied unique, which would mean a lower result. What would cause this discrepancy and how would I identify those user IDs that don't show up in the 2nd query?

Thanks

**

UPDATE - 11:14 am est

**

Hi All

I sincerely apologize as I should've taken the trouble to reproduce this in my local environment. But I just wanted to see if there was a general consensus about this. Here are the full details:

The query is a result of an inner join between 2 tables. One has this information:

TABLE ACTIVITY  (NO PRIMARY KEY)
UserID  int   (not Nullable)
JoinDate    datetime
Status  tinyint
LeaveDate   datetime
SentAutoMessage tinyint
SectionDetails  varchar

And here is the second table:

TABLE USER_INFO  (CLUSTERED PRIMARY KEY)
UserID  int    (not Nullable)
UserName    varchar
UserActive  int
CreatedOn   datetime
DisabledOn      datetime

The tables are joine开发者_JAVA百科d on UserID and the UserID being selected in the original 2 queries is the one from the TABLE ACTIVITY.

Hope this clarifies the question.

This is not technically an answer, but since I took time to analyze this, I might as well post it (although I have the risk of being down voted).

There was no way I could reproduce the described behavior.

This is the scenario:

declare @table table ([user id] int)

insert into @table values 
(1),(1),(1),(1),(1),(1),(1),(2),(2),(2),(2),(2),(2),(null),(null)

And here are some queries and their results:

SELECT COUNT(User ID) FROM @table --error: this does not run
SELECT COUNT(dsitinct User ID) FROM @table --error: this does not run
SELECT COUNT([User ID]) FROM @table --result: 13 (nulls not counted)
SELECT COUNT(distinct [User ID]) FROM @table --result: 2 (nulls not counted)

And something interesting:

SELECT user --result: 'dbo' in my sandbox DB
SELECT count(user) from @table --result: 15 (nulls are counted because user value
                                             is not null)
SELECT count(distinct user) from @table --result: 1 (user is the same  
                                                     value always)

I find it very odd that you are able to run the queries exactly how you described. You'd have to let us know the table structure and the data to get further help.

how would I identify those user IDs that don't show up in the 2nd query

Try this query

SELECT UserID from tableUserID Where UserID not in (SELECT Distinct User ID from tableUserID)

I think there will be no row.

Edit:

User is a reserved keyword. Do you mean UserID in your requests ?

Ray : Yes

I tried to reproduce the problem in my environment and my conclusion is that given the conditions you described, the result from the first query can not be higher than the second one. Even if there would be NULL's, that just won't happen.

Did you run the query @Jean-Charles sugested?

I'm very intrigued with this, please let us know what turns out to be the problem.