CREATE TABLE members
(member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,gender CHAR(1) NOT NULL
);
INSERT INTO members VALUES
(1,'John','m'),
(2,'Yoko','f'),
(3,'Paul','m'),
(4,'Linda','f'),
(5,'George','m'),
(6,'Ringo','m');
SELECT * FROM members;
+-----------+--------+--------+
| member_id | name | gender |
+-----------+--------+--------+
| 1 | John | m |
| 2 | Yoko | f |
| 3 | Paul | m |
| 4 | Linda | f |
| 5 | George | m |
| 6 | Ringo | m |
+-----------+--------+--------+
SELECT gender
, COUNT(*) total
FROM members
GROUP
BY gender;
+--------+-------+
| gender | total |
+--------+-------+
| f | 2 |
| m | 4 |
+--------+-------+
SELECT SUM(CASE WHEN gender = 'm' THEN 1 ELSE 0 END) m
, SUM(CASE WHEN gender = 'f' THEN 1 ELSE 0 END) f
FROM members;
+------+------+
| m | f |
+------+------+
| 4 | 2 |
+------+------+