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 fr

Solution 1:

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@tabletable ([user id] int)

insertinto@tablevalues 
(1),(1),(1),(1),(1),(1),(1),(2),(2),(2),(2),(2),(2),(null),(null)

And here are some queries and their results:

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

And something interesting:

SELECTuser--result: 'dbo' in my sandbox DBSELECTcount(user) from@table--result: 15 (nulls are counted because user valueisnotnull)
SELECTcount(distinctuser) 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.

Solution 2:

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 notin (SELECTDistinctUser 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

Solution 3:

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.