Q

Obtaining COUNTs for males and females in one SQL query

For my table the gender column has only two values; F and M. I would like to do a count of both genders in one SQL query.

For my table the gender column has only two values; F and M. I would like to do a count of both genders in one query. Something like:

select 
count(p.ps_gender) as Male,
count(p1.ps_gender) as Female, 
p.ps_area as Area
from personal p, personal p1....

Could you suggest anything?

Thank you for asking. Here are three suggestions:

select coalesce(p.ps_area,p1.ps_area) as ps_area
     , Male
     , Female
  from ( 
       select ps_area
            , count(*) as Male
         from personal  
        where ps_gender = 'M' 
       group
           by ps_area  
       ) as p
full outer
  join ( 
       select ps_area
            , count(*) as Female
         from personal  
        where ps_gender = 'F' 
       group
           by ps_area  
       ) as p1
    on p1.ps_area = p.ps_area 

This looks like where you might have been headed, with p and p1 aliases. Note the FULL OUTER JOIN, which handles situations where there are only males or only females.

select ps_area 
     , count( case when ps_gender='M'
                   then 1 end ) as Male
     , count( case when ps_gender='F'
                   then 1 end ) as Female
  from personal 
group
    by ps_area

This one's nice because it gives one row per ps_area. Also because it will still work if there are only males or only females.

select ps_area 
     , ps_gender
     , count(*) as area_gender_count
  from personal 
group
    by ps_area
     , ps_gender

This, in my opinion, is the best solution. It, too, adequately (if not elegantly) handles the situation where there are only males or only females. But in addition, both of the first two queries will barf (that's a technical term, which means to produce incorrect results) if you need to add a third value besides M and F.

This was first published in May 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

2 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close