Query with count and pivot of the same column

Question

I try to count occurrences of a cell content (column2 - values aa, bb, cc) related to url (column1 - values url1, url2, etc.). Columns are long (a few thousands rows).

I want to group content by url, pivot by column 2. I try to use query formula for this but I do something wrong. I made a dummy sheet with source table and desired effect. There's also my formula that returns error.

=query(B2:C,"select B, count(C) where B is not null group by B pivot C",0)

How can I get desired result using QUERY?

Note: I know how to do it different way but I want to learn more about Query.

You can play in this file: https://docs.google.com/spreadsheets/d/1dNIjfK253gEA0v7zpdeuubOUX8BHg1R3wKnvYe544Eg/copy

I checked also Query language reference but it did not help: https://developers.google.com/chart/interactive/docs/querylanguage#overview

player0 player0 · Accepted Answer · 2021-04-06T17:51:16

use:

=QUERY({B2:C, C2:C}, 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1 
  pivot Col3", 0)

or with 0's:

=INDEX(IFERROR(
 QUERY({B2:C, C2:C}, "select Col1,count(Col2) 
 where Col1 is not null group by Col1 pivot Col3 label Col1 ' '", 0)*1, 
 QUERY({B2:C, C2:C}, "select Col1,count(Col2) 
 where Col1 is not null group by Col1 pivot Col3", 0)))

Query with count and pivot of the same column

1 Answers