1

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.