Coutnting Records in each category 1

[Mayoor]

Im trying to count the number of males, the number of females and then the number of people who havent specified either, i.e people will NULL in the gnder field. I need to table to appear like this

Male 23
Female 34
None Specified 45

Can anyone help?

 

[misterhux]

have you tried using something like union selects :
select u.male, u.female from (count(field) as male where field = "male" Union select count(field) as female where = "female"...) as u
(note this wont actually run, but its a start)

 

[bdreed35]

Try this as a guide:

select gender, count(gender)
from table
where gender = 'Male'

union all

select gender, count(gender)
from table
where gender = 'Female'

union all

select 'None Specified', count(gender)
from table
where gender <> 'Male'
and gender <> 'Female'

~Brian

 

[Pattycake245]

How about this

select gender =
case gender
when 'm' then 'Male'
when 'f' then 'Female'
else 'None Specified' end, count(*) cnt from table group by gender order by cnt

Tim

 

[Pattycake245]

Or:

select gender =
case gender
when 'male' then 'Male'
when 'female' then 'Female'
else 'None Specified' end, count(*) cnt from table group by gender order by cnt

Not sure how the gender field is displayed in your table

Tim