Query to get a count of the female and male customers from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to get a count of the female and male customers from a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:07:00

npandis writes "Hi. I have a customer table where I select from a combo box field the sex (male or female). I would like to run an SQL query that will give me a count of the male and female customers.

Thank you
npandis"

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-01 : 23:33:30

If you want a result like this
-Male- -Female-
125 87
then do:
select count(sex) from customers where sex = 'male' AS Male,Count(sex) from customers where sex = 'female' as Female

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-01-01 : 23:49:26

Oops, sorry! My SQL is off!
select 0 as male,count(sex) as female from patients where sex = 'female' union select count(sex) as male,0 as female from patients where sex = 'male'
will give the correct result, though it's a very cumbersome way to do this. One of the gurus can certainly come up with a better answer!

Sarah

Nazim
A custom title

1408 Posts

Posted - 2002-01-01 : 23:54:46

Simon am sorry to say that doesnt works at all. you could have checked for proper syntax of select.

npandis this should do what u require

select count(CASE upper(sex )
WHEN 'MALE' then 1 end),
count(CASE upper(sex)
WHEN 'FEMALE' then 1 end) END
from tablename

HTH

npandis
Starting Member

6 Posts

Posted - 2002-01-02 : 04:31:40

quote:

Simon am sorry to say that doesnt works at all. you could have checked for proper syntax of select.

npandis this should do what u require

select count(CASE upper(sex )
WHEN 'MALE' then 1 end),
count(CASE upper(sex)
WHEN 'FEMALE' then 1 end) END
from tablename

HTH

Hi, and thank you for your help.

When I am entering the statement, it gives me the following error:

Syntax error (missing operator) in query expression '(count(CASE upper(sex )WHEN 'MALE' then 1 end)'.
When I click ok on the syntax error box the cursor is blinking on the word upper.

Any ideas? The database is in access 2000.

Thank you

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 07:18:46

Am Really Sorry nPandis

select count(CASE upper(sex )
WHEN 'MALE' then 1 end),
count(CASE upper(sex)
WHEN 'FEMALE' then 1 end)
from tablename

there was an additional End to the select which i overlooked

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-02 : 08:01:17

quote:

The database is in access 2000.

So, when you came to an SQL Server website and it said

We answer questions on Microsoft SQL Server here. No Oracle, no Access, no ASP, no mySQL. Just Microsoft SQL Server.

you didn't see fit to mention you were using access ?

What you did was waste the time of people that were good enough to help you.

Access doesn't use CASE like SQL Server does, go look up the syntax for switch

In future, ask the right questions, in the right places, and specify the problem properly.

Damian

lfmn
Posting Yak Master

141 Posts

Posted - 2002-01-02 : 08:52:18

not to mention that there is a forum specifically for Access!!!

quote:

quote:

The database is in access 2000.

So, when you came to an SQL Server website and it said

We answer questions on Microsoft SQL Server here. No Oracle, no Access, no ASP, no mySQL. Just Microsoft SQL Server.

you didn't see fit to mention you were using access ?

What you did was waste the time of people that were good enough to help you.

Access doesn't use CASE like SQL Server does, go look up the syntax for switch

In future, ask the right questions, in the right places, and specify the problem properly.

Damian