SQLTeam.com | Weblogs | Forums

Help with this query needed

I have an inquiry with x questions and a series of questionnaires filled out. I want to get the amount of questions answered per form out of the total of all question. Every given answer is recorded in the InquiryAnswer table. The logical query for me would be:

select count(b.ID) as done, count(a.ID) as TotalQuestions, b.InquiryID
from Questions a
     left outer join InquiryAnswers b on a.ID = b.QuestionID
group by b.InquiryID

But this returns the same amount for "done" as for "total"? What am I doing wrong?

IF OBJECT_ID('tempdb..#Questions') IS NOT NULL
DROP TABLE #Questions

CREATE TABLE #Questions (id int, question NVARCHAR(10))
insert into #Questions (id,question) values (1, 'Question 1')
insert into #Questions (id,question) values (2, 'Question 2')
insert into #Questions (id,question) values (3, 'Question 3')

IF OBJECT_ID('tempdb..#InquiryAnswers') IS NOT NULL
DROP TABLE #InquiryAnswers

CREATE TABLE #InquiryAnswers (id int, InquiryID int, questionID int)
insert into #InquiryAnswers (id, InquiryID, questionID) values (1, 1, 1)
insert into #InquiryAnswers (id, InquiryID, questionID) values (2, 1, 2)
insert into #InquiryAnswers (id, InquiryID, questionID) values (3, 2, 3)

so for this example I want the query to return:

done    total    InquiryID
2       3        1
1       3        2

select
sum( case when "done" then 1 else 0 end ) ,
sum( case when "total" then 1 else 0 end )

harishgg1, I guess you mean this:

select Sum(CASE WHEN b.ID is not null THEN 1 ELSE 0 END) as done,
       Sum(CASE WHEN a.ID is not null THEN 1 ELSE 0 END) as tot,
       b.InquiryID
from #Questions a
    left outer join #InquiryAnswers b on a.ID = b.questionID
group by b.InquiryID

This returns:

  done      total       InquiryID
   2          2            1
   1          1            2

while the total should be 3!

hi

i only have sql server 2008 .. so here is my sql

SELECT * FROM    
          (   SELECT COUNT(InquiryID) AS total   FROM   #InquiryAnswers) a
        , (   SELECT  InquiryID, COUNT(*) AS done  FROM  #InquiryAnswers  GROUP BY InquiryID) b;

image

Thank you, harishgg1. That works, although formally speaking these are 2 independent queries combined in one. I thought it could be done in kind of "one operation", just from how I conceive this problem... but I couldn't. Leaves me with a strange feeling as this problem looks to me rather trivial, but I have to move on and will use your solution. Cheers, Martin

hi Martin

this could be done like that !!! i think i know what you mean !!!

hope this helps :slight_smile: ..you can test and see what happens OR I can do it foryou

if you use left outer join ..what that means
is nulls will come ..

SELECT * FROM
( SELECT *FROM #Questions )  d
LEFT OUTER  JOIN
(
SELECT * FROM    
          (   SELECT COUNT(InquiryID) AS total   FROM   #InquiryAnswers) a
        , (   SELECT  InquiryID, COUNT(*) AS done  FROM  #InquiryAnswers  GROUP BY InquiryID) b ) C
 d.ID = D.QuestionID

Ok, thank you harishgg1 foryour involvement, but I actually have to move on. This one is somewhat more complicated to understand. The solution you provided before did its job and I do not have the time to investigate on certain doubts that I still might have .