MySQL

help with (hopefully) simple nested query

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jun 2009

Posts: 7

Reputation: Mandler is an unknown quantity at this point

Solved Threads: 0

Mandler

Offline

Newbie Poster

help with (hopefully) simple nested query

Jun 18th, 2009

I'm writing a query to summarize survey results. There are two tables...

1) The Profiles table contains the users' answers. There are four columns: ProfileID (for the unique key), QuestionID (a number representing canned questions from another table), Answer (a number representing multiple choice answers from another table), and UserID.

2. The Users table has three columns that apply to the query: UserID (the same as that in the Profiles table) and Gender.

What I want to do is present a tally of how many people answered a question in a particular way. For instance, if I wanted to list people's favorite ice cream flavors, I'd want to see:

CHOCOLATE 7
VANILLA 4
ETC.

With QuestionID 4 corresponding to the Ice Cream Flavor question, that was pretty straight-forward with...

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
	SELECT Profiles.Answer, COUNT(Profiles.Answer) FROM Profiles, Users
	WHERE Profiles.QuestionID =4 AND Users.UserID = Profiles.UserID 
	GROUP BY Profiles.Answer;
	SELECT Profiles.Answer, COUNT(Profiles.Answer) FROM Profiles, Users
	WHERE Profiles.QuestionID =4 AND Users.UserID = Profiles.UserID 
	GROUP BY Profiles.Answer;

Now what I want to is group the results by Gender...

CHOCOLATE
MALE 4
FEMALE 3
VANILLA
MALE 2
FEMALE 2

How do I write the nested query to group the first query's results by Gender?

Thanks in advance.

•

Join Date: May 2009

Posts: 187

Reputation: jbisono is an unknown quantity at this point

Solved Threads: 24

jbisono

Offline

Junior Poster

Re: help with (hopefully) simple nested query

Jun 19th, 2009

Try that

Help with Code Tags

(Toggle Plain Text)

SELECT     Profiles.answer, users.gender, COUNT(Profiles.answer) AS count
FROM         Profiles INNER JOIN
                      users ON users.userid = Profiles.userid
GROUP BY users.gender, Profiles.answer

Regards.

If your already resolved your issue, flag it as solved.
José Bisonó