CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

Query with count and pivot of the same column

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)

enter image description here

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

Upvotes: 0

Views: 1120

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 2

Related Questions