MySQL query group by and count on multiple conditions

Question

I have a MySQL table (voters) of voter data in the following format:

id | name | gender | const

I need to output the following:

const | no. of female voters | no. of male voters | total no. of voters

order by no. of female voters.

I could only come up with this query:

select const,count(*) from voters where gender='f' group by const order by count(*) desc

How do I get other two counts too? const means constituency, gender can be either 'm' or 'f'

M Khalid Junaid · Accepted Answer · 2017-12-18 17:51:54Z

1

In Mysql you can do this

select const,
count(*),
sum(gender='f') female_voters,
sum(gender='m') male_voters
from voters 
group by const 
order by count(*) desc

answered Dec 18, 2017 at 17:51

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Cool. I just did order by sum(gender='f') and it worked. Thanks.

@M Khalid Junaid Is there any simple way to also show percentage of female voters in another column?

@Naveed you can calculate % of females as (sum(gender='f')/count(*)) * 100

Yes but how to display it as a column

In above query add it like(sum(gender='f')/count(*)) * 100 as somecolumnname

|