Google Sheets Query – How to Aggregate and Pivot Simultaneously

google sheetsgoogle-sheets-query

Hey I am trying to transform a table from this:

Into this:

Basically for each "Type" I want the totals for the different values for "Exists" which right now is just TRUE and FALSE.

But I can only get to this so far:

I did that using the following QUERY

SELECT B, C, COUNT(C) GROUP BY B,C

I know the next step requires a PIVOT but I'm having some fundamental misunderstanding about how PIVOT works to get anywhere. If you could help me out with some understanding and not just give me the answer it would be great.

Best Answer

The select argument of QUERY doesn't allow to use the same column to group, aggregate and to pivot data.

The workaround is to add an auxiliary column that repeats the data from C, let say it will be D, the select argument will be

=query(A1:D6,"SELECT B, COUNT(C) GROUP BY B PIVOT D")

Related Solutions

Google Sheets Query for Non-Empty Cells

Please try:

=QUERY('Form Responses 1'!A:N,"SELECT C,E,G,H where G is not null or H is not null order by C,E")

Google Sheets – Create a Pivot Table with Custom Formula Per Row

As a pre-processing step, I suggest representing "yes" answers as 1 and "no" as 0; this makes aggregation easier.

Then in a pivot table, you can simply create a calculated field =sum(better)/sum(had), assuming "had" and "better" are your column headers.

However, I don't see the need for pivot table report when you are essentially using it as a query. Sorting within a pivot table report can be problematic, and in any case doesn't happen automatically. Here is a query solution, again assuming that the responses are recorded as 0-1 numbers. It automatically sorts by ratio (better)/(had prior), in descending order.

=query(A:C, "select A, sum(C)/sum(B) group by A order by sum(C)/sum(B) desc", 1)

The last argument makes it explicit that the input data hasa= a header row.

And here is a more elaborate version, with labels on columns.

=query(A:C, "select A, sum(C)/sum(B) group by A order by sum(C)/sum(B) desc label A 'Name', sum(C)/sum(B) 'Ratio'", 1)

Best Answer

Related Solutions

Google Sheets Query for Non-Empty Cells

Google Sheets – Create a Pivot Table with Custom Formula Per Row

Related Topic