How to aggregate tabular data

Question

I have displayed some values in Google Sheets as below.

   Name  Answer

    A      yes
    A      No
    B      No
    A      Yes
    X      Yes
    A      Yes

I want to aggregate those values and display the result in another table like this:

   Name  Total Right Wrong Right%

    A      4     3     1     75
    B      1     0     1      0
    X      1     1     0     100

How can I do this, using Spreadsheet Formulas?

Answer 1

Take a look at this example sheet where I used this formula to generate the desired output

=query(ArrayFormula(if(len(query({filter(Sheet1!A2:B, len(Sheet1!B2:B)),filter(ArrayFormula(if(Sheet1!B2:B="yes", "Right", "Wrong")), len(Sheet1!B2:B))}, "select Col1, count(Col2) group by Col1 pivot Col3"))=0, 0, (query({filter(A2:B, len(Sheet1!B2:B)),filter(ArrayFormula(if(Sheet1!B2:B="yes", "Right", "Wrong")), len(Sheet1!B2:B))}, "select Col1, count(Col2) group by Col1 pivot Col3")))), "select Col1, (Col2+Col3)*1, Col2, Col3,  (Col2/(Col2+Col3))*100 label (Col2+Col3)*1 'Total', (Col2/(Col2+Col3))*100 'Right %', Col1 'Names' ")