MySQL 如何使用sql查询计算比率？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27253333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to calculate ratio using sql query?
提问by san_code
I have a table like below:
我有一张如下表：
ID Name Department Gender
1 Crib MA MALE
2 Lucy Bsc FEMALE
3 Phil Bcom MALE
4 Ane MA FEMALE
I have 1000 row of records like this. I want to find the ratio from column Gender( MALE & FEMALE) of all students.
我有 1000 行这样的记录。我想从所有学生的性别（男性和女性）列中找到比例。
I need a query to perform this.
我需要一个查询来执行此操作。
回答by Fabien TheSolution
MySQL 5.5.32 Schema Setup:
MySQL 5.5.32 架构设置：
CREATE TABLE table1
(`ID` int, `Name` varchar(4), `Department` varchar(4), `Gender` varchar(6))
;
INSERT INTO table1
(`ID`, `Name`, `Department`, `Gender`)
VALUES
(1, 'Crib', 'MA', 'MALE'),
(2, 'Lucy', 'Bsc', 'FEMALE'),
(3, 'Phil', 'Bcom', 'MALE'),
(4, 'Ane', 'MA', 'FEMALE')
;
Query 1:
查询 1：
SELECT sum(case when `Gender` = 'MALE' then 1 else 0 end)/count(*) as male_ratio,
sum(case when `Gender` = 'FEMALE' then 1 else 0 end)/count(*) as female_ratio
FROM table1
结果：
| MALE_RATIO | FEMALE_RATIO |
|------------|--------------|
| 0.5 | 0.5 |
回答by S. Wirth
Try something like this
尝试这样的事情
select sum(case when gender = 'MALE' then 1 else 0 end) / count(*) * 100 as perc_male,
sum(case when gender = 'FEMALE' then 1 else 0 end) / count(*) * 100 as perc_female
from students
回答by Hans Kilian
How about
怎么样
select gender, count(*)
from table
group by gender
then it's very simple to calculate the ratio yourself.
那么自己计算这个比例就很简单了。
回答by Aaron Averett
This should give you the actual ratio, and should work with little or no modifcation in MySQL and SQL Server. You may have to modify the cast statement a little - my MySQL is rusty, and I think it may handle that slightly differently.
这应该为您提供实际比率，并且在 MySQL 和 SQL Server 中几乎不需要修改即可工作。您可能需要稍微修改一下 cast 语句 - 我的 MySQL 生锈了，我认为它的处理方式可能略有不同。
SELECT
(CAST((SELECT COUNT(*) FROM tblName WHERE Gender='MALE') AS FLOAT) /
CAST((SELECT COUNT(*) FROM tblName WHERE Gender='FEMALE') AS FLOAT))
AS ratioMaleFemale;
回答by Sebri Zouhaier
You're pretty close:
你很接近：
select (select count(*)
from table where gender='MALE' )/count(*)*100 as percentage_male,
(select count(*)
from table where gender='FEMALE' )/count(*)*100 as percentage_female
from table;
