Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using the SQL GROUP BY clause for counting combinations

Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause

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

This was last published in April 2009

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close