CODEWITHSUNDEEP
sqlsql-serverdistinct
Daniel_Kamel
Daniel_Kamel

Reputation: 619

SQL count(username) and count(distinct username) returns same result

If I execute the following query in my SQL Server:

SELECT COUNT([UserName]), COUNT(DISTINCT [UserName]) 
FROM [UsersTable]

I get the following (and expected) result:

enter image description here

Now if execute this query:

SELECT [TenantId], COUNT([UserName]), COUNT(DISTINCT [UserName]) 
FROM [UsersTable] 
GROUP BY [TenantId]

I get this result:

enter image description here

which implies that all the username are distinct which is false, however I can't understand why.

Upvotes: -3

Views: 85

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112682

It looks like the UserNames are unique per TenantId, i.e., they are unique in each group. And since the COUNT is calculated for each group, the result is correct. COUNT(UserName) and COUNT(DISTINCT UserName) must be equal for the groups.

Upvotes: 4

Related Questions