COUNT with IF FROM DISTINCT values MySQL

Question

I am trying to calculate the how many users are female and how many male, after doing DISTINCT on the UserID

SELECT
COUNT(IF("Gender" = 'female', 1, NULL)) as Ufemale,
COUNT(IF("Gender" = 'male', 1, NULL)) as Umale
FROM (SELECT DISTINCT UserID FROM user_stats where Year='2019' and Account='P') as UID

If I execute the

SELECT DISTINCT UserID FROM user_stats where Year='2019' and Account='P'

It returns the unique UserID. However, if I combine it with count gender part, it returns Zero.

Here is how the values looks like

UserID            |  Gender
-----------------------------
2018359084885123  |  male
1925823664195671  |  female
2033134076795519  |
2122445674469149  |  female
2315129265210413  |  female
2018359084885123  |  male
2122445674469149  |  female

And the aim is to show

Ufemale  |  Umale
-------------------
   3     |    1

Answer 1

Try this:

Tables

drop table if exists test;

create table test (userid bigint, gender char(6));

insert into test values
(2018359084885123,'male')
,(1925823664195671,'female')
,(2033134076795519, null)
,(2122445674469149,'female')
,(2315129265210413,'female')
,(2018359084885123,'male')
,(2122445674469149,'female');

Query

select
    sum(case when gender = 'female' then 1 else 0 end) as ufemale,
    sum(case when gender = 'male' then 1 else 0 end) as umale
from
(select distinct userid, gender from test) x

Result

ufemale umale
3       1

Example: https://rextester.com/JLQ19855

Answer 2

There is no Gender column in the results of the subquery:

SELECT DISTINCT UserID FROM user_stats where Year='2019' and Account='P'

as you may think.
What you are doing is comparing 2 strings: "Gender" and 'female' which are obviously not equal so this:

IF("Gender" = 'female', 1, NULL)

returns NULL and COUNT(NULL) returns 0.
Where is the Gender column?
Is it in user_stats table or in another table like Users?
If it is in user_stats then you need to write the query like this:

select
  sum(t.gender = 'female') ufemale,
  sum(t.gender = 'male') umale
from (
  select distinct userid, gender 
  from user_stats 
  where Year='2019' and Account='P'
) t

If it is in the users table then you will need a join first:

select
  sum(u.gender = 'female') ufemale,
  sum(u.gender = 'male') umale
from (
  select distinct userid, gender 
  from user_stats 
  where Year='2019' and Account='P'
) t inner join users u
on u.userid = t.userid