Member Avatar for Mandler

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
  • 157 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Mandler
Member Avatar for jbisono

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.

Member Avatar for Mandler

It's perfect.
Thank you very much.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.