MySQL JOIN COUNTS 來自另一個表

我有這兩張桌子：

表成員

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

表成員_電子郵件

id  member  email
1   1   jsmith@mail.com
2   1   johnsmith@jsa.org
3   2   danvers@marvels.com
4   4   llane@dailyplanet.com

我想知道有多少不同性別的成員有電子郵件，如下表：

total   male    female
3       1       2

我已經用過

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
)

但結果

total   male    female
3       2       2

因為它無法區分 e.member。

有誰知道如何做到這一點？

您可以在加入前選擇 DISTINCT 成員 ID

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', 'jsmith@mail.com'),
  ('2', '1', 'johnsmith@jsa.org'),
  ('3', '2', 'danvers@marvels.com'),
  ('4', '4', 'llane@dailyplanet.com');

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`

總計| 男| 女性
----: | ---: | -----:
 3 | 1 | 2

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/285813