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 Answer 1

Reset to default
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
6
  • 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, 2018 at 20:45
  • @BarryRogerson I made a "slight" change to my answer
    – Rubén ♦
    May 12, 2018 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, 2018 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, 2018 at 21:59
  • 1
    @DavidLeal From the answer: "the workaround is to add an auxiliary column that repeats the data from C, let say it will be D"
    – Rubén ♦
    Jul 17, 2021 at 5:20

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.