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:
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
(
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
)