Counting Issue 1

[Signit]

I am trying to count the total number of questions answered vs the total number of possible questions in a questionere I have created. I am storing the information in three tables and am running my sql in a stored proc on sql server. Here's my sql

  select count(a.wfa_question_id) as 'number_of_questions_answered',
         count(b.wfa_question_id) as 'total_number_of_questions'
    from wfa_answer a,
         wfa_question b,
         wfa_section c
   where a.wfa_header_id = @wfa_header_id
     and a.wfa_question_id = b.wfa_question_id
     and b.wfa_section_id = c.wfa_section_id
  group by b.wfa_section_id

I am pretty sure I know why this is happening but I am unsure of how to fix it ... Thank you in advance.

 

[r937]

when you say COUNT(column), you are counting the number of occurrences in the result set

when you join three tables, you get a result set based on how they are joined, and then if you count the number of occurrences of values, you should -- if there are no nulls -- get the same answer for any column in the result set

if you want separate counts for separate tables, the best way is to use separate queries

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Signit]

I was afraid that was the answer. Thanks for taking the time to reply.