MySQL JOIN COUNTS from another table

countjoin;MySQL

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

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?

Best Answer

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', '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`

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

db<>fiddle here

Related Question