MySQL JOIN COUNTS from another table

Question

I have this two tables:

Table members

id full_name sex
1 John Smith 1
2 Carol Danvers 2
3 Clark Kent 1
4 Lois Lane 2
5 Diana Prince 2

Table member_emails

id member email
1 1 [email protected]
2 1 [email protected]
3 2 [email protected]
4 4 [email protected]

I would like to know how many distinct member sex count who has emails, like this table:

total male female
3 1 2

I've already use

 SELECT 
 COUNT(DISTINCT e.member) AS total,
 SUM(CASE
 WHEN m.sex = 1
 THEN 1
 ELSE 0
 END) AS "male",
 SUM(CASE
 WHEN m.sex = 2
 THEN 1
 ELSE 0
 END) AS "female"
FROM member_emails e
 JOIN members m ON m.id = (
 SELECT DISTINCT e.member
 FROM members
 WHERE id = e.member
)

But it resulted

total male female
3 2 2

As it can't distinct e.member.

Does anyone have any ideas how to do this?

Answer 1

You can SELECT the DISTINCT member ids before joining

CREATE TABLE members (
 `id` INTEGER,
 `full_name` VARCHAR(15),
 `sex` INTEGER
);
INSERT INTO members
 (`id`, `full_name`, `sex`)
VALUES
 ('1', 'John Smith', '1'),
 ('2', 'Carol Danvers', '2'),
 ('3', 'Clark Kent', '1'),
 ('4', 'Lois Lane', '2'),
 ('5', 'Diana Prince', '2');
CREATE TABLE member_email (
 `id` INTEGER,
 `member` INTEGER,
 `email` VARCHAR(21)
);
INSERT INTO member_email
 (`id`, `member`, `email`)
VALUES
 ('1', '1', '[email protected]'),
 ('2', '1', '[email protected]'),
 ('3', '2', '[email protected]'),
 ('4', '4', '[email protected]');

SELECT
COUNT(*) AS total,
SUM(`sex`= 1) AS male
,SUM(`sex`= 2) AS female
FROM 
(SELECT DISTINCT `member` FROM member_email) m_e
INNER JOIN members m ON m_e.`member` = m.`id`

total | male | female
----: | ---: | -----:
 3 | 1 | 2

db<>fiddle here