CODEWITHSUNDEEP
mysql
DomingoSL
DomingoSL

Reputation: 15494

How to obtain percents of two sums directly from a MySQL query?

Im calculating the percent of men and woman in my database using first this query:

SELECT sum(case when `gender` = 'M' then 1 else 0 end) as male
     , sum(case when `gender` = 'F' then 1 else 0 end) as female
FROM userinfo 
WHERE id in (10,1,5)

Then in php i calculate the percents. But I wonder, is there a way to get directly the percents from the query?

Upvotes: 0

Views: 33

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65274

SELECT sum(case when `gender` = 'M' then 1 else 0 end) as male
     , 100*sum(case when `gender` = 'M' then 1 else 0 end)/count(*) as malepct
     , sum(case when `gender` = 'F' then 1 else 0 end) as female
     , 100*sum(case when `gender` = 'F' then 1 else 0 end)/count(*) as femalepct
FROM userinfo 
WHERE id in (10,1,5)

... assuming you don't run this over an empty rowset (division by zero)

If you e.g. want to replace invalid (division by zero) values by -1 use

if(ifnull(count(*),0)>0,100*sum(case when `gender` = 'M' then 1 else 0 end)/count(*),-1) as malepct

Upvotes: 2

Related Questions