时间戳

首页 > 解决方案 > 查询给定数据集中的男女比例#MySQL

mysql - 查询给定数据集中的男女比例#MySQL

问题描述

如何编写查询给定数据集的男女比例

select gender
     , sum( case when gender = 'male'
                 then 1 else 0 end )    as male
     , sum( case when gender = 'female'
                 then 1 else 0 end )    as female
  from adult;

这给出了单独的计数但没有比率。

标签： mysqldatabasesumpivotaverage

解决方案

您可以COUNT()结合使用IF()来获取性别的计数和比例。

示例表内容：

+----+-------+--------+
| id | name  | gender |
+----+-------+--------+
|  1 | John  | male   |
|  2 | Jane  | female |
|  3 | Stacy | female |
|  4 | Karen | female |
|  5 | Bob   | male   |
+----+-------+--------+

使用以下查询：

SELECT
    COUNT(IF(gender = 'male', 1, NULL)) count_male,
    COUNT(IF(gender = 'female', 1, NULL)) count_female,
    COUNT(IF(gender = 'male', 1, NULL))/COUNT(IF(gender = 'female', 1, NULL)) as ratio
FROM
    users;

你会得到以下结果：

+------------+--------------+--------+
| count_male | count_female | ratio  |
+------------+--------------+--------+
|          2 |            3 | 0.6667 |
+------------+--------------+--------+

推荐阅读