Counting the same column with different where conditions

Question

I have a table called employees, whose schema looks like the following:

mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

I just want to query the count of emp_no for Males (where generder = 'M') and females (where generder = 'F').

I want the results like:

+--------+--------+
| Male   | FeMale |
+--------+--------+
| 300    | 120    |
+--------+--------+

Any suggestion on how to do that in a single query?

Nope. The other article has the `Male` and `Female` in different rows not columns. Although I am sure this question is a duplicate of something on SO ^^ (Also the other question is for SQL Server which makes a duplicate even less likely...) — Hans, Jul 04 '17 at 15:21

score 1 · Answer 1 · edited Jul 04 '17 at 15:24

1

SELECT SUM(gender = 'M') AS Male, SUM(gender = 'F') AS Female
FROM employees

edited Jul 04 '17 at 15:24

Strawberry

33,750
13
40
57

answered Jul 04 '17 at 15:19

Hans

447
3
10

score 0 · Answer 2 · answered Jul 04 '17 at 15:23

0

select sum(case when gender = 'm' then 1 else 0 end) male,
sum(case when gender = 'f' then 1 else 0 end) female,
from employees

you could try something like that

answered Jul 04 '17 at 15:23

Kevin

2,258
1
32
40

You can save all the `case when` stuff since MySQL evaluates bools to 0 or 1 – Hans Jul 04 '17 at 15:25

Counting the same column with different where conditions

2 Answers2