Skip to content
Advertisement

Division of 2 values in mysql

Hello I’m a newbie in SQL.

I have a table of workers and I want to calculate the avg num of female workers. I want to divide 2 tables that I create, but when I do the division the value of the new table is NULL and the other is 0.

SELECT COUNT(SEX) AS NUMOFWOMEN
FROM project_2.stuff
WHERE SEX = WOMAN;

SELECT COUNT(SEX) AS TOTALNUMOFWORKERS
FROM project_2.stuff;

SELECT (CAST(NUMOFWOMEN AS FLOAT) / CAST(TOTALNUMOFWORKERS AS FLOAT)) * 100 AS avgnum;

SELECT CAST(avgnum AS FLOAT) AS AVGNUMOFWOMEN

Advertisement

Answer

If you want to compute the ratio of rows where sex = 'woman' over your whole dataset, you can just use avg():

select avg(sex = 'woman') as women_ratio
from project_2.stuff

Rationale: MySQL evaluates expression sex = 'woman' as 1 if the condition succeeds, else 0. Averaging this value over the whole table gives you the result you want – you can multiply that by 100 if you want a percentage.

Note that there is no need to convert to float or the-like. MySQL happily takes decimal average of integers.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement