Count number of surveys that had 2 specific questions both answered Yes-CodePudding

I am trying to count how many surveys came back but only if both question 7 and 9 were answered yes. I can get the count of 1 or the other but not if both questions are Yes in the same survey (this is as far as I got):

SELECT
count(if(question.id = '7' and value = 'Yes',1,null)) as A
count(if(question.id = '9' and value = 'Yes',1,null)) as B
FROM questions

Survey(id)

Question (Question.id)	Answer (value=Yes/No/Null)
1	No
2	Yes
3
4	No
5	Yes
6	No
7	Yes
8
9	Yes

CodePudding user response：

I hope that the following query helps you:

Select Count(D.surveyId) CNT From
(
  Select  surveyId,Count(*) cn
  From questions
  Where (qid=7 Or qid=9) And (answer='yes')
  Group By surveyId
  Having Count(*)=2
) D

See a demo from db<>fiddle.

CodePudding user response：

How about just simply the solution by getting count of all the valid scenarios for question ID 7 and 9 And then Grouping based on Survey ID.

SELECT COUNT(*) 
FROM questions 
WHERE questions.id IN (7,9) AND questions.value IN ('Yes')
GROUP BY SurveyID HAVING COUNT(*) > 1