我想为不同的值计算相同的字段,例如：
user{user_id,gender}
性别可以有明显的男性或女性:)
我想得到所有男性和女性的数量,即
COUNT(male) COUNT(female)
4 16
但我很困惑,因为他们来自同一性别coloumn谢谢
最佳答案
试试这个行的结果：
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 |