How to optimize this in MySQL?

Question

I have table structure as displayed in first table.

And want to fetch Both Male and Female Counts in a single query so that request will go only for one time onto the server.

Answer 1

This is what you need to do:

select gender,
       count(case when age between 0 and 20 then 1 else null end) Age_0_20,
       count(case when age between 21 and 40 then 1 else null end) Age_21_40
from yourtable
group by gender

Adjust accordingly :)

Update, with clarifications

Note that COUNT aggregate function only counts non-null values. Thus, the else values in the case must be NULL. The When value returns 1 but it could just be any non-null value.

Some people implement this by using SUM:

select gender,
       sum(case when age between 0 and 20 then 1 else 0 end) Age_0_20,
       sum(case when age between 21 and 40 then 1 else 0 end) Age_21_40
from yourtable
group by gender

The result is going to be absolutely the same.