mysql - 从 mysql 表中的同一列获取计数？

Question

我想对不同值的相同字段进行计数，例如:

user{user_id, gender}

性别可以有明显的男性或女性:)

我想计算所有男性和女性的数量，即

COUNT(male)   COUNT(female)  
   4               16

但我很困惑，因为他们来自同一个gender coloumn 谢谢

Answer 1

尝试按行结果:

SELECT gender, COUNT(User_id) AS count
FROM User
GROUP BY gender;

输出:

| gender | count |
|--------|-------|
|      F |     4 |
|      M |     2 |

试试这个以获得总计的行式结果:

SELECT  (IFNull(gender,'Total')) AS gender,
COUNT(User_id) AS Count
FROM User
GROUP BY gender
WITH rollup;

输出:

| gender | Count |
|--------|-------|
|      F |     4 |
|      M |     2 |
|  Total |     6 |

尝试按列结果:

SELECT
  COUNT(CASE WHEN gender = 'M' THEN User_id END) AS males,
  COUNT(CASE WHEN gender = 'F' THEN User_id END) AS females,
  COUNT(*) AS Total
FROM User;

输出:

| males | females | Total |
|-------|---------|-------|
|     2 |       4 |     6 |

See this SQL Fiddle