SQL: How use a subquery value in the query

I have a table of test answers where answer_result=1 if the answer was correct.   There are 50-100 different questions represented in the table with hundreds of responses each.  Columns include: question_id, test_id,answer_result, answer, correct_answer.

For each question within a given test_id, I'd like to:

1. Count the number of answers
2. Count the number of correct answers
3. Provide the percentage of correct answers
4. Limit to a specific test_id (for example, where test_id = 382)

question_id  test_id   total  correct   percent
2238                   382     402        326        81%
2239                   382     532        235        44%
....

How can I do this in one query statement?
StarDusterIIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
WIthout table DDL and sample data it's hard to craft a sample SQL. But you simply use GROUP BY and COUNT(*) for all results and SUM(CASE WHEN answer_result=1 THEN 1 ELSE 0 END) for counting the correct answers. The percentage is a simple division.
Mike EghtebasDatabase and Application DeveloperCommented:
Does your data looks something like this:
question_id, test_id, answer_result, answer, correct_answer
2238	     382	   B	           A	   NO
2238	     382	   A	           A	   YES
2238	     382	   C	           C	   YES

Open in new window

StarDusterIIAuthor Commented:
eghtebas, yes that's basically it.  Numbers rather than letter grades and answer_result=1 for correct and 0 for incorrect but basically the same.
question_id, test_id, answer_result, answer, correct_answer
2238	     382	   0	           1	   3
2238	     382	   1	           2	   2
2238	     382	   1	           4	   4

Open in new window


I should also add that I don't really want to repeat the subquery to get total number of questions again when doing the division.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
This is a tested solution but make sure table name and fields match.
Select d.question_id
     , d.test_id, d.total
     , d.Cor_total
     , cast(d.Cor_total as decimal)/ cast(d.total as decimal) as Per
From (Select question_id
         , test_id, count(question_id) As total
         , sum(correct_answer) As Cor_total 
      From #testAnswers
      Group By question_id, test_id) as d

Open in new window


FYI, this is a query with a derived table. What is significant about derived table, first a defintion:
A derived table is probably the form of table expression that most closely resembles a subquery—only a subquery that returns an entire table result. You define the derived table’s inner query in parentheses in the FROM clause of the outer query, and specify the name of the derived table after the parentheses.

When we have, say (note the example below has error and it is included for the purpose of our discussion):
Select d.question_id
   , d.test_id
   , count(question_id) As total
   , Sum(correct_answer) As Cor_total
   , cast(Cor_total as decimal)/ cast(total as decimal) as Per
From #testAnswers
Group By question_id, test_id

The logical order SQL Server runs this query is as follows:

From #testAnswers
Group By question_id, test_id
Select d.question_id
   , d.test_id
   , count(question_id) As total
   , Sum(correct_answer) As Cor_total
   , cast(Cor_total as decimal)/ cast(total as decimal) as Per

So, to have Cor_total and total defined before hand, it must be calculated in advance in FROM clause in order for them to be used in the Select Clause. True that these are calculated in the same select but SQL Server doesn't allow same-time calculation (unlike MS Access under certain conditions).

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StarDusterIIAuthor Commented:
Works perfectly.  Appreciated the explanation very much... well done!
Mike EghtebasDatabase and Application DeveloperCommented:
some additional info related to Derived tables:

Table Expressions:
Table expressions are named queries. You write an inner query that returns a relational result set, name it, and query it from an outer query. T-SQL supports four forms of table expressions:
- Derived tables                         (created and used in code)
- Common table expressions (CTEs)      (“)
- Views                              (created in code or in SSMS stored as database objects)
- Inline table-valued functions            (“)

The inner query is not allowed to have an ORDER BY clause unless you use the TOP or OFFSET-FETCH option in the inner query, the ORDER BY serves a meaning that is not related to presentation ordering; rather, it’s part of the filter’s specification. So if the inner query uses the TOP or OFFSET-FETCH option, it’s allowed to have an ORDER BY clause as well. But then the outer query has no presentation ordering guarantees if it doesn't have its own ORDER BY clause.

A minor correction on my last post:
... SQL Server doesn't allow same-time calculation (unlike MS Access under certain conditions).

should read:

... SQL Server doesn't allow “all-at-once”  calculation (unlike MS Access under certain conditions).

According the book I am reading.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.