Need Help With SQL - 2 Columns of Data

Databases
1

I have a table with lots of columns and from that table I’ve selected 2 columns of data which can be seen below.

The 2 columns are:
user_email and letter

user_email contains every person’s email address and letter column contains a value which is either A or B. Each person’s email can appear in any number of rows.

example: table_X

bob@hotmail.com, B
bob@hotmail.com, B
bob@hotmail.com, A
chris@hotmail.com, A
chris@hotmail.com, A
sue@hotmail.com, B
jess@yahoo.com, A
jess@yahoo.com, A
jess@yahoo.com, A
jess@yahoo.com, B

I’m trying to find out 3 different values, the number of users (I assume using COUNT) who:

  1. Have both A and B
  2. Have Only B
  3. Have Only A

This one’s stumped me. Any ideas?

2 
SELECT COUNT(CASE WHEN a > 0 AND b > 0 THEN 'bofadem' END) AS bofadem
     , COUNT(CASE WHEN a = 0 AND b > 0 THEN 'only b'  END) AS only_b
     , COUNT(CASE WHEN a > 0 AND b = 0 THEN 'only a'  END) AS only_a
  FROM ( SELECT user_email
              , COUNT(CASE WHEN letter='A' THEN 'A' END) AS a
              , COUNT(CASE WHEN letter='B' THEN 'B' END) AS b
           FROM daTable
         GROUP
             BY user_email ) AS counts
3 

(
    SELECT 'both' as type, 
           COUNT(lettera.user_email) as number
      FROM (   SELECT 
             DISTINCT user_email
                 FROM table-with-lots-of-cols
                WHERE letter = 'A' )
           lettera
INNER JOIN
           (   SELECT 
             DISTINCT user_email
                 FROM table-with-lots-of-cols
                WHERE letter = 'B' )
           letterb
        ON lettera.user_email = letterb.user_email
)
UNION ALL
(
    SELECT 'A' as type, 
           COUNT(lettera.user_email) as number
      FROM (   SELECT 
             DISTINCT user_email
                 FROM table-with-lots-of-cols
                WHERE letter = 'A' )
           lettera 
      LEFT
OUTER JOIN
           (   SELECT 
             DISTINCT user_email
                 FROM table-with-lots-of-cols
                WHERE letter = 'B' )
           letterb
        ON lettera.user_email = letterb.user_email   
     WHERE letterb.user_email IS NULL
)
UNION ALL
(
    SELECT 'B' as type, 
           COUNT(letterb.user_email) as number
      FROM (   SELECT 
             DISTINCT user_email
                 FROM table-with-lots-of-cols
                WHERE letter = 'A' ) 
           lettera 
     RIGHT
OUTER JOIN
           (   SELECT 
             DISTINCT user_email
                 FROM table-with-lots-of-cols
                WHERE letter = 'B' )
           letterb
        ON lettera.user_email = letterb.user_email   
     WHERE lettera.user_email IS NULL
)
4

Thanks a lot to both of you for replying, very good answers, appreciate your time!!! Rudy I went with yours, spot on - majestic! What an expert!

5

majestic? whoa, hold on there

thanks, though

:slight_smile: