SQLのcountを使った条件分岐

CREATE table person(
id int not NULL auto_increment primary key,
sex VARCHAR(255)
);

I insert data.

INSERT INTO person(sex)VALUES('m');
INSERT INTO person(sex)VALUES('m');
INSERT INTO person(sex)VALUES('m');
INSERT INTO person(sex)VALUES(null);
INSERT INTO person(sex)VALUES(null);
INSERT INTO person(sex)VALUES('f');
INSERT INTO person(sex)VALUES('f');
INSERT INTO person(sex)VALUES('f');
INSERT INTO person(sex)VALUES('f');

These are current data.

SELECT * FROM person;

SELECT COUNT(*) FROM person;

=> 9

The COUNT() does not count null.

SELECT COUNT(sex) FROM person;

=> 7

sex='m' is 3. Other false data return null.
The COUNT() does not count null.

SELECT COUNT(sex='m' or null) FROM person;

=> 3

sex='f' is 4. Other false data return null.
The COUNT() does not count null.

SELECT COUNT(sex='f' or null) FROM person;

=> 4

We can count male, female in both way.

SELECT
  COUNT(sex='m' or NULL),
  COUNT(sex='f' or NULL)
FROM person;

SELECT
  SUM(CASE WHEN sex='m' then 1 else 0 end),
  SUM(CASE WHEN sex='f' then 1 else 0 end)
FROM 
 person; 

We can check SQL cost like this.
It is same in this case.

SHOW LOCAL STATUS like 'Last_query_cost';