CODEWITHSUNDEEP
sql
matcha_latte
matcha_latte

Reputation: 21

count where values appear in another table sql

I have a form that I want to find out how many people who fill in the form have an account.

The form goes into an SQL table and has a column for the email address and I want to compare it with the column in another table that contains all of the accounts.

table1: (form entries)

email_address

table2: (customer account table)

email_address

I need an SQL to tell me how many people from table1 had an account. In this example the answer would be 2.

I got this far but when I tested it on a few different tables I got some results higher than the amount of accounts I had:

SELECT DISTINCT
  COUNT(email_address)
FROM
  table2
WHERE
  EXISTS
  (SELECT 1 
   FROM table1 
   WHERE table1.email_address = table2.email_address );

Upvotes: 2

Views: 99

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

How about a plain join:

SELECT COUNT(DISTINCT t1.email_address)
FROM table1 t1
INNER JOIN table2 t2
    ON t1.email_address = t2.email_address;

This assumes that the email address in the second table is unique, on the grounds that an account would likely only be registered once. If so, then the inner join would filter off all email address entries which did not have accounts.

See the @Yogesh answer if you want to keep using the EXISTS approach.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

distinct should be inside count() :

SELECT COUNT(DISTINCT t1.email_address) 
FROM table1 t1
WHERE EXISTS (SELECT 1 
              FROM table2 t2
              WHERE t1.email_address = t2.email_address
              );

Upvotes: 1

Related Questions