Using the SQL GROUP BY clause for counting combinations
By Rudy Limeback, SQL Consultant, r937.com
SearchOracle.com
I am running a market survey and all the survey data is saved in the database. I need a query for counting the number of rows in which option "1" is selected for question "1," option "2" for question "1" and so on for all questions and options. I need to specify a few conditions here. I have to match distinct IDs of three tables and display the result for each particular ID.
You've given very little information about your tables, so the following solution explains how to get the counts without going into details about how your tables are joined.
SELECT x.question_number
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
, y.option_number
, COUNT(*) AS number_of_rows
FROM your joins go here
GROUP
BY x.question_number
, y.option_number
The count that is produced for this particular GROUP BY clause is the count for every single unique combination of question_number and option_number values.
The only wrinkle on the solution is if certain option numbers were not chosen. You cannot count what's not there, if you're using an INNER JOIN; a LEFT OUTER JOIN should be used instead. But I cannot give you a sample query because I don't know what your three tables look like.
Oracle White Papers: Fusion Middleware