Using postgres tablefunc crosstab() to count incorrect answers

Question

I have a view (let's call it 'fruit') that has a column of correct answers and the associated incorrect answers from a multiple choice exam, and I would like to count which incorrect answers were chosen most frequently (the easily confused ones). The view looks something like this:

correct_answer | wrong_response
-------------------------------
apple          |   pear
apple          |   pear
apple          |   banana
banana         |   apple
banana         |   pear
banana         |   pear
banana         |   pear
pear           |   apple

And what I would like is a pivot table that counts the wrong responses respective to the correct answer, such that the columns represent the correct answer and the rows represent the counts for the wrong answers.

wrong_response | apple | banana | pear
---------------------------------------
apple          | 0     | 1      | 1
banana         | 1     | 0      | 0
pear           | 2     | 3      | 0

I've been here before with this function, but I wasn't trying to count things that time. Any help would be much appreciated!

EDIT: For future folks, both of these solutions work! The conditional aggregation, however, is more flexible. The crosstab solution only works if you have exactly every possibility in the query. For example, if you exclude pear (or add kiwi), the crosstab solution returns an error. The conditional aggregation returns results regardless if you exclude (or add currently non-existing) records. Thanks for the help, all.

Gin · Answer 1 · 2019-06-12T21:19:57.227

Assume you've already done: CREATE EXTENSION tablefunc;

Then to achieve what you want by crosstab() function is then:

SELECT *
FROM crosstab('SELECT wrong_response,
                      correct_answer,
                      count(*)
               FROM fruit
               GROUP BY wrong_response, correct_answer 
               ORDER BY wrong_response',

              'SELECT correct_answer
               FROM fruit
               GROUP BY correct_answer
               ORDER BY correct_answer')

AS (wrong_answer varchar(20),
    apple bigint,
    banana bigint,
    pear bigint);

Above code would give you following result which is what you want:

Note the 0 is output as null here, in order to get exactly what you want, you just need to modify the select a bit:

SELECT
    wrong_answer,
    coalesce(apple, 0) as apple,
    coalesce(banana, 0) as banana,
    coalesce(pear, 0) as pear
FROM crosstab('SELECT wrong_response,
                      correct_answer,
                      count(*)
               FROM fruit
               GROUP BY wrong_response, correct_answer 
               ORDER BY wrong_response',

              'SELECT correct_answer
               FROM fruit
               GROUP BY correct_answer
               ORDER BY correct_answer')

AS (wrong_answer varchar(20),
    apple bigint,
    banana bigint,
    pear bigint)

Above would get you to what you want:

score 1 · Accepted Answer · answered Jun 12 '19 at 18:57

If you know the columns you can use conditional aggregation:

select wrong_response,
       count(*) filter (where correct_answer = 'apple') as apple,
       count(*) filter (where correct_answer = 'pear') as pear,
       count(*) filter (where correct_answer = 'banana') as banana
from t
group by wrong_response;

Using postgres tablefunc crosstab() to count incorrect answers

2 Answers2

Linked