SQL help - Count and sort 2 columns with multiple choices

They have: 426 posts

Joined: Feb 2005

I am building a simple system that registers users. I am only using one table to store everything. Each row consists of a userid, email address, phone number, gender, hair length

Gender can be male or female and hair length can be long, medium or short.

What I am trying to do is show how many males and females have long medium or short hair in a table like the below

See imageshack image here

but I cant figure out the SQLso that I can do this in one query. So far I am here:

SELECT COUNT(1) as 'Gender',
(SELECT COUNT(1) FROM user AS t2 WHERE t2.hair = 's') AS Short,
(SELECT COUNT(1) FROM user AS t2 WHERE t2.hair = 'm') AS Medium,
(SELECT COUNT(1) FROM user AS t2 WHERE t2.hair = 'l') AS Long
FROM user GROUP BY gender

Any help appreciated.

They have: 426 posts

Joined: Feb 2005

So I think I need to redesign my database tables anyway actually. Gender and hair length should be in separate tables and be foreign keys in a new user table.

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

To be honest for simplicity save I would have broken them up to separate SQL statements, one to count (and group) by gender, and then a second one to count (and group) by hair.

-Greg

They have: 11 posts

Joined: Apr 2013

to count the data of a single attribute we use count() function and to sort the data use order by function

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.