Grand total in Google spreadsheet pivot queries

Question

I have a sample Google spreadsheet here. In Sheet2 of that spreadsheet, I created a summary using the query:

=Query(Sheet1!A1:E24, "Select A, Count(E) GROUP BY A Pivot C")

But I want to sum of each surveyors count at the next row and its grand total like in PivotTable shown in 3rd sheet. Is it possible to calculate the grand total using queries itself? Can anyone please help.

Answer 1

This is an old question, but I just figured this out. You can use ={} to join the arrays resulting from multiple queries into one function. Creating the array with {} is described here: Using arrrays in Google Sheets

The easiest way to set it up is to do each part separately, then recombine them into one formula.

1st part is the query you did first.

=Query(Sheet1!A1:E24, "Select A, Count(E) GROUP BY A Pivot C")

2nd part is the column of totals. Put this formula in the top cell of the column just to the right of the 1st query results.

=Query(Sheet1!A1:E24, "Select Count(E) GROUP BY A label Count(E) 'Grand Total'")

3rd part is the bottom row totals. Put this under the first query results. There's no way to remove the headers row on a pivot, so instead, transpose the results of a non-pivot query.

=transpose(Query(Sheet1!A1:E24, "Select Count(E) GROUP BY C label Count(E) 'Grand Total'"))

4th part is the final Total cell at the bottom right. The '' for the label is to remove any label and just have the total in one cell.

=Query(Sheet1!A1:E24, "Select Count(E) Label Count(E) ''")

If it all looks the way you want it, now you put it together into one formula to replace the original query and delete the others. All contained within {}, separate columns with , and rows with ;.

={Query(Sheet1!A1:E24, "Select A, Count(E) GROUP BY A Pivot C"),Query(Sheet1!A1:E24, "Select Count(E) GROUP BY A label Count(E) 'Total'");transpose(Query(Sheet1!A1:E24, "Select Count(E) GROUP BY C label Count(E) 'Total'")),Query(Sheet1!A1:E24, "Select Count(E) Label Count(E) ''")}