Sql-server – SQL Return Count of Answers with 0’s for Choices from another table

join;sql server

My question went dead after one unhelpful answer on SO here

I need this to be optimized because it will be called often. It is going into a stored procedure.

I am given @slide (tblILDSlide.id) and @sessionid (tblILDSession.id)

I need my results to look like this:

--lets say students had answered a(3),b(10),c(0),d(5) in a survey (test)
 |Answer Counts 
0|3
1|10
2|0 --this needs to show as a zero and not be skipped
3|5

Since questions will each have different numbers of possible answers I need to have the rows seeded from the @@rowcount of Choice for the QID that is here:

**dbo.tblSurveyAnswerChoice
 QID (int, not null)
 Sequence (int, null)
 Choice (nvarchar(100), null) --this column will vary and is for choices
 --like true/false, Yes, No, Red Blue, 
 --or even Completely Agree,Somewhat Agree...,Completely Disagree

To get the QID I have to use @slide and:

**dbo.tblILDSlide
 id (PK, int, not null)
 slide_type(FK, int, not null)
 question_id(FK, int, null)
 media_url(nvarchar(100), null)

I need a row for every choice (with zeros for choices with no responses). The responses are kept here:

 --there can be more than one response per question 
 --like for multiple-multiple choice (check all that apply)
**dbo.tblResponses
 response_id(FK, int, not null)
 answer_sequence (int, null) --accepts 1,2,3 etc for choice
 other (nvarchar(50), null) --for text answers

To get them I have to use response_id which I can get by using @sessionid AND @slide here:

**dbo.tblSlideResponseInfo
 id (PK, int, not null)
 user_id(nvarchar(50), not null)
 slide_id (FK, int, not null)
 response_date (datetime, not null)
 session_id (FK, int, not null)

Copied from the post at stackoverflow: My problem is when there is a possible choice with no answers I get no count. So I know I need to grab the possible choices which are available [in the other table …] I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that, but I can't figure it out.

Best Answer

(copying my own answer from the same question on StackOverflow)

Your post already includes a good answer: "I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that."

Here is one way to do so:

select choice, ifnull(countanswervalue,0) 
from tblsurveyanswerchoice
left join 
    (select answer_sequence, count(answer_sequence) as countanswervalue
    from tblslideresponses
    group by answer_sequence) 
    as countanswer on countanswer.answer_sequence=tblsurveyanswerchoice.sequence
/* the where clause is what you already posted */
where qid in (select question_id from tblidslide where id = @slide)

The use of left join ensures that any choice in the first table will be listed. By default, left join would return NULL for items not existing in the second table, but ifnull lets you replace NULL with 0.

NOTE: ifnull in MySQL should be replaced by isnull in TSQL

Related Question