0

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...

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.

2
Contributors
2
Replies
4
Views
8 Years
Discussion Span
Last Post by Mandler
0

Try that

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.