Combine two small queries (that group by different values) into one query

Question

Please take a look at the following table (called response). It shows what response a respondent has given to a question and answer. ``` questionid answerid respondentid 1 10 1 1 11 2 1 11 4 1 12 3 1 12 5 2 20 1 2 20 2 2 21 2 2 22 1 2 22 4 2 23 1 2 23 3 2 24 4 3 30 2 3 30 3 3 30 4 3 31 1 ``` We can run the following SQL: ``` select questionid, answerid, count(respondentid) as noOfRespondentsToQuestionAndAnswer from response group by questionid, answerid ``` ... which will tell us how many respondents answered each combination of question+answer. We can also do: ``` select questionid, count(distinct respondentid) as noOfRespondentsToQuestion from response group by questionid ``` ... which will tell us how many distinct respondents have answered each question. I would like to combine the two selects to ONE and let the number of distinct respondents be represented on more than one row for each questionid (which would be necessary, as it's only based on question and not also on answer). S…

Accepted Answer

```
select one.questionid, answerid,
       noOfRespondentsToQuestionAndAnswer,
       noOfRespondentsToQuestion
FROM (
select questionid, answerid,
       count(respondentid) as noOfRespondentsToQuestionAndAnswer
from response
group by questionid, answerid) AS one
JOIN (
select questionid, count(distinct respondentid) as noOfRespondentsToQuestion
from response
group by questionid) AS two
WHERE one.questionid = two.questionid;
```

Combine two small queries (that group by different values) into one query

Solution

Problem