How to calculate ratio using sql query?

Question

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.

I need a query to perform this.

Callus

Which DBMS are you using? You tagged sql server, mysql and oracle. – Iinden

Answer 1

SQL Fiddle

MySQL 5.5.32 Schema Setup:

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:

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 |

Braise

If you want to get decimal points, adjust as follows: SELECT CAST(SUM(CASE WHEN `Gender` = 'MALE' THEN 1 ELSE 0 END) AS FLOAT)/CAST(COUNT(*) AS FLOAT) as male_ratio, – Hazing

In SQL Server, to get decimal points, I needed to multiply by 1.0, like this: SELECT SUM(CASE WHEN `Gender` = 'MALE' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) as male_ratio,. Casting to float didn't work for me. – Rodi

Answer 2

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

Leboeuf

Answer 3

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.

SELECT 
    (CAST((SELECT COUNT(*) FROM tblName WHERE Gender='MALE') AS FLOAT) / 
     CAST((SELECT COUNT(*) FROM tblName WHERE Gender='FEMALE') AS FLOAT)) 
    AS ratioMaleFemale;

Accusation

Answer 4

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;

Macklin

Answer 5

use tempdb
go


Create  Table       Employee(
                    EmpID int NOT NULL,
                    EmpName Varchar (50),
                    Gender Char (50), 
                    Salary int,
                    City Char(50)
                    )

Insert Into Employee Values (1, 'Arjun',    'M',    75000,  'Pune'),
                            (2, 'Ekadanta', 'M',    125000, 'Bangalore'),
                            (3, 'Lalita',   'F',    150000, 'Mathura'),
                            (4, 'Madhav',   'M',    250000, 'Delhi'),
                            (5, 'Visakha',  'F',    120000, 'Mathura')

Select* From Employee

Select                COUNT (Gender) As [Total Emp],
                      (Select   COUNT(Gender) From Employee
                      Where     Gender = 'M') *100/Count (*) As [Male Ratio],
                      (Select   COUNT (Gender) From Employee
                      Where     Gender = 'F') *100/Count (*) As [Female Ratio]
From                  Employee

Sheronsherourd

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. – Dalrymple