CODEWITHSUNDEEP
sqlsql-serversql-server-2008count
abramlimpin
abramlimpin

Reputation: 5077

Count All Unique and Existing Records

Membership

ID    MemberID    UserID    UserID2
1     7           1         0
2     6           4         1
3     4           6         12
4     2           8         18
5     1           1         4
6     3           7         0

I used this SQL statement:

SELECT COUNT(DISTINCT CHECKSUM(UserID, UserID)) FROM Membership

but I am getting only 6

I want to get all the unique records both for columns UserID and UserID2.

1, 4, 6, 7, 8, 12, 18 (excluding 0) = 7

Any suggestions?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want to count the distinct values, try doing a union all first:

select count(distinct UserId)
from ((select UserId from MemberShip
      ) union all
      (select UserId2 from Membership
      )
     ) mu
where UserId <> 0;

EDIT:

The following version is mentioned in the comments:

select count(*)
from ((select UserId from MemberShip
      ) union
      (select UserId2 from Membership
      )
     ) mu
where UserId <> 0;

I prefer the union all version because I generally avoid union as much as possible. The extra step of removing duplicates always imposes additional performance -- although useful here, it is often not needed. That is why I prefer the union all version.

Although not simpler (in my opinion), this version has one possible small advantage in some databases. If the Membership table has separate indexes on UserId and UserId2, then it is easier to take advantage of these indexes for the query. Note that this does not eliminate the de-duping (because the lists of users from both columns need to be de-duped), but it does (potentially) reduce the volume of members in the de-duping step.

Upvotes: 3

Related Questions