0

Hey I am trying to transform a table from this:

enter image description here

Into this:

enter image description here

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:

enter image description here

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.

2 Answers 2

2

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)
0
0

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

1
  • 1
    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?
    – Blindspots
    Commented Mar 12 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.