How to write query for this given result?-CodePudding

Table "question":

q_id	s_id question
8	1
9	1
10	1

Table "result":

id	q_id	s_id	answer
1	8	1	yes
2	9	1	no
3	10	1	yes
4	8	1	no
5	9	1	yes
6	10	1	yes

I want the result to look like this how can I do this can anyone help me or suggest what I can do ??

have to take s_id from user as input

question	yes	no
like coffee??	1	1
like water??	1	1
like tea??	2	0

CodePudding user response：

In the future it would help if you make a reproducible example using something like https://dbfiddle.uk/.

If you want to pivot like this, you will need to use GROUP BY and some aggregation. I would include s_id and q_id in your result as well, just in case you would like more than one student's result, of if there are identical questions with different q_id. I commented them out in this example:

SELECT 
  -- question.s_id, 
  -- question.q_id,
  question.question, 
  SUM(CASE WHEN result.answer = 'yes' THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN result.answer = 'no' THEN 1 ELSE 0 END) AS no
FROM question 
JOIN result
ON  question.s_id = result.s_id
AND question.q_id = result.q_id
WHERE question.s_id = 1 -- your input
GROUP BY 
  -- question.s_id, 
  -- question.q_id,
  question.question

db<>fiddle here