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?