EXPERT RESPONSE
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.
|
