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.
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.