SQL help - Count and sort 2 columns with multiple choices
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
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.
benf posted this at 16:41 — 3rd February 2012.
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.
Good Value Professional VPS Hosting
Greg K posted this at 08:59 — 10th May 2012.
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
RohanJaiswal posted this at 06:12 — 24th April 2013.
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.