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.

| improve this question | |
1

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")
| improve this answer | |
  • Sorry, this answer doesn't get me to the second picture in my post which is where I'm trying to go. – Barry Rogerson May 12 '18 at 20:45
  • @BarryRogerson I made a "slight" change to my answer – Rubén May 12 '18 at 21:00
  • thanks for the update; that works now. So is there an elegant way to make "D" on the fly in the query or must it be done quite manually ? My actual problem, for which this is a simplification, has many sorts of "Exists" columns so it would be bad to have do duplicate each one. – Barry Rogerson May 12 '18 at 21:56
  • 1
    @BarryRogerson you could use an "embedded/inline array" -> webapps.stackexchange.com/a/80611/88163. Bear in mind that instead of Col1 notation should be used instead of A notation for column names -> webapps.stackexchange.com/q/91919/88163 – Rubén May 12 '18 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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