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).

So, I would like a result like the following:

questionid,answerid,noOfRespondentsToQuestionAndAnswer,noOfRespondentsToQuestion
1   10 1 5
1   11 2 5
1   12 2 5
2   20 2 4
2   21 1 4
2   22 2 4
2   23 2 4
2   24 1 4
3   30 3 4
3   31 1 4

Is it possible to acheive this with just one query?

Answer 1

You didn't specify which type of database, which would simplify this, but from a pure sql idea, not using any analytics, it can be done, but you will lose efficiency.

select questionid, answerid, 
(select a.count(*) FROM datatable a WHERE a.questionid=questionid AND a.answerid=answerid), 
(select b.count(*) FROM datatable b WHERE b.questionid=questionid)
FROM datatable ORDER BY questionid, answerid;

Answer 2

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;