Simple query optimization

Question

Hey guys, forgive me if this is too simple a question. I basically want a count of the number of males and females in a database. So I know two simple queries will accomplish this, such as:

select count(*) from table where gender='male'
select count(*) from table where gender='female'

However, this seems very inefficient since I know that the queries below are both the same query:

select count(*) from table where gender='female'
select count(*) from table where gender<>'male'

Is there an optimal way to retrieve this information, without having to go through each row in the database twice?

Answer 1

select sum(case when gender='male' then 1 end) as MaleCount,
    sum(case when gender='female' then 1 end) as FemaleCount
from table

Answer 2

Yes, use a case statement instead.

SELECT
    COUNT(CASE gender WHEN 'male'   then 1 END) AS Males
    COUNT(CASE gender WHEN 'female' then 1 END) AS Females
FROM table

Answer 3

You may use a group by clause.

select gender, count(gender) from table group by gender;

Answer 4

(select count(*) from table where gender='male')
UNION
(select count(*) from table where gender='female')

first row is males, second row is females...

Answer 5

You could also use a GROUP BY:

SELECT gender, Count(1) as genderCount
FROM   table
GROUP BY gender

This will result in something like this:

gender       genderCount

Male         10

Female       15

Answer 6

Well, RedFilter and erwin atuli probably gave the answer to your question, but if you are just concerned about the performance, then you should put an index on the gender column and the performance of those queries should be fine, so you can execute them multiple times.

If you are using oracle, then a Bitmap Index should be used to index that kind of data.

Many databases cache such aggregation results anyway.

Answer 7

@redfilter: if the table contains no entries your query would just return NULL-Values and not 0 (as integer)