Group questions by answers with SQL-CodePudding

SQL Server table:

userId	QuestionId	Question	AnswerId	Answer
32	98	What is the total salary in your family?	380	4000
32	99	How many are brothers?	385	5
33	98	What is the total salary in your family?	382	3000
33	99	How many are brothers?	385	5
34	98	What is the total salary in your family?	382	3000
34	99	How many are brothers?	385	5
35	98	What is the total salary in your family?	381	5000
35	99	How many are brothers?	384	4
36	98	What is the total salary in your family?	381	5000
36	99	How many are brothers?	383	3
37	98	What is the total salary in your family?	381	5000
37	99	How many are brothers?	383	3
38	98	What is the total salary in your family?	380	4000
38	99	How many are brothers?	385	5
39	98	What is the total salary in your family?	380	4000
39	99	How many are brothers?	385	5
41	98	What is the total salary in your family?	381	5000
41	99	How many are brothers?	383	3

I want to find the list of the number of common answers given to the questions

Example:

salary: 5000 brothers: 3 count = 3 user

Question1Id	Question2Id	Answer1	Answer2	count
98	99	3000	5	2
98	99	4000	5	3
98	99	5000	3	3
98	99	5000	4	1

CodePudding user response：

Here you go:

select
  a.questionid, b.questionid,
  a.answer as answer1, b.answer as answer2, count(*) as count
from mytable a
join mytable b on a.userid = b.userid
where a.questionid = 98
  and b.questionid = 99
group by a.questionid, b.questionid, a.answer, b.answer