SQL Server query to count

Question

I have two tables Invitations and Users, both table contain some emails.

I want to count those emails which are present in the Invitation table but not present in the Users table

Invitation

InvitationID    Email
-------------------------------------
    1           [email protected]
    2           [email protected]
    3           [email protected]

Users

UserName                  IsActive
-------------------------------------
[email protected]             InActive
[email protected]       Active
[email protected]           InActive

I tried it like this

SELECT  COUNT(*) FROM Invitations, Users                                     
where Invitations.Email <> Users.UserName

I want like this

Count=1

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard - stop using it — marc_s
– marc_s, Commented Nov 30, 2012 at 10:35

podiluska · Accepted Answer · 2012-11-30 10:37:33Z

8

You're getting the wrong answer because you're counting the wrong thing.

Imagine two records, a and b in each table. a<>b and b<>a so you'll get 2, not the 0 you're expecting.

Try this instead

Select count(*) 
from Invitations
     left join Users                                     
     on Invitations.Email = Users.UserName
where Users.UserName is null

answered Nov 30, 2012 at 10:37

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Obl Tobl · Accepted Answer · 2012-11-30 10:41:03Z

3

You can try this one:

SELECT COUNT(*)
FROM Invitation
WHERE Email not in (
    SELECT u.username
    FROM Users
)

answered Nov 30, 2012 at 10:41

Obl Tobl

5,6828 gold badges44 silver badges67 bronze badges

Comments

Michał Powaga · Accepted Answer · 2012-11-30 10:45:24Z

3

select count(*)
from Invitation i
where not exists (select 1 from Users u where i.email = u.userName)

This is simplest way in my opinion, you can event read it: count all rows in Invitation table where not exists a row in table Users where userName is equal email.

edited Nov 30, 2012 at 10:45

answered Nov 30, 2012 at 10:39

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

Collectives™ on Stack Overflow

SQL Server query to count

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related