如何使用sql查询来计算比率? [英] How to calculate ratio using sql query?

问题描述

我有一个如下表:

ID   Name   Department Gender
1    Crib     MA        MALE
2    Lucy     Bsc       FEMALE
3    Phil     Bcom      MALE
4    Ane      MA        FEMALE

我有1000行这样的记录.我想从所有学生的性别(男性和女性)栏中找到比率.

I have 1000 row of records like this. I want to find the ratio from column Gender( MALE & FEMALE) of all students.

我需要一个查询来执行此操作.

I need a query to perform this.

推荐答案

SQL提琴

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')
;

查询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

结果 :

Results:

| MALE_RATIO | FEMALE_RATIO |
|------------|--------------|
|        0.5 |          0.5 |

这篇关于如何使用sql查询来计算比率?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！