# How to Aggregate and Pivot Simultaneously With Google 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.

The QUERY function's query statement doesn't allow using the same column to group, aggregate, and pivot data.

The workaround is to add an additional column that is a duplicate of the data in C. This can be done in the range or in the formula.

### Duplicate in Range

This formula requires creating a new data column D that is a duplicate of C:

``````=QUERY(A1:D7,"SELECT B, COUNT(C) GROUP BY B PIVOT D", 1)
``````

### Duplicate in Formula

In this approach the duplication is done using an embedded/inline array within the formula therefore it doesn't require changing the source data. You must use Col1 notation with this approach. eg. Col1, Col2, Col3, instead of A, B, C.

Example using your data range, A1:C7:

``````=QUERY({A1:C7, C1:C7}, "SELECT Col2, COUNT(Col3) GROUP BY Col2 PIVOT Col4", 1)
``````

I was having the same problem and I did that:

=QUERY(QUERY(A7:C7;"SELECT B, C, COUNT(C) GROUP BY B,C");"SELECT Col1, sum(Col3) group by Col1 pivot Col2")

I'm just using your first result as the range of the query function that I add

• The accepted answer does it with one query. Is there any additional benefit to your approach? Also your ranges appear incorrect. Have you tested this formula? Commented Mar 12 at 22:04