Having trouble with mysql variables, what am I doing wrong here?

Question

See below and test here.
I've been pulling my hair out over this for a couple of hours now. I've searched many posts and as best I can tell everything is correct.
I'm having trouble with the IF comparison and the @malefemale variable. I can pull this off by declaring variables in a stored procedure without any problems, I just want to get the damn thing working in the online editor above to share with a friend, and in this case, just to get the damn thing working at all. What am I missing?

create table test(id int, gender varchar(10), salary int);
insert into test(id, gender, salary) values (1, 'male', 40000), (2, 'male', 50000),  (3,'male', 40000), (4, 'female', 60000), (5, 'female', 60000), (6,'female', 40000);

set @m =0;
set @f =0;
set @malefemale = 'same';

select count(*) into @m from test WHERE gender like 'male' and salary >= 50000;
select count(*) into @f from test WHERE gender like 'female' and salary >= 50000;

if @m > @f then
    @malefemale = 'male';
else if @f > @m then
    set @malefemale ='female';
endif

select @malefemale;

Answer 1

Variables are utterly unnecessary for this query:

select (case sign(sum(gender = 'male') - sum(gender = 'female') )
             when 1 then 'male'
             when -1 then 'female'
             when 0 then 'same'
        end)
from test 
where gender like 'male' ;

If you are running this as MySQL code, then your problem is probably not the variables, but the if. It is only allowed in programming blocks -- think stored procedures, functions, and triggers.

Answer 2

According to the mysql documentation if statements are only allowed in stored programs.

The IF statement for stored programs implements a basic conditional construct.

Documentation

However. There is an IF you could use.

create table test(id int, gender varchar(10), salary int);
insert into test(id, gender, salary) values (1, 'male', 40000), (2, 'male', 50000),  
(3,'male', 40000), (4, 'female', 60000), (5, 'female', 60000), (6,'female', 40000);

set @m =0;
set @f =0;

select count(*) into @m from test WHERE gender like 'male' and salary >= 50000;
select count(*) into @f from test WHERE gender like 'female' and salary >= 50000;


select if (@m > @f, 'male', 'female')

For explanation:
select if ([CONDITION], [THEN], [ELSE])

And because I can't comment your question. The downvote may be because of your unnecessary swearing. I can understand that you're frustrated but maybe one "damn" would be enough.

Answer 3

I agree with Gordon Linoff' response but, as I can not comment yet, I would just like to correct two things in the code:

select (case sign(sum(gender = 'male') - sum(gender = 'female') )
             when 1 then 'male' 
             when -1 then 'female'
             when 0 then 'same'
        end)
from test;

The condition was added for when the amount of gender is equal (same), which is also an expected response. The condition where gender like 'male' was removed, that was limiting the search to only the gender 'male'.