CODEWITHSUNDEEP
google-sheetspivotgoogle-sheets-formula
Elad Benda
Elad Benda

Reputation: 36672

Query parse error: CANNOT_GROUP_WITHOUT_AGG

I try to convert one table to another.

What is wrong with my syntax?

=QUERY(B21:D24, "select A,C where A is not null group by A pivot B",1)

Unable to parse query string for Function QUERY parameter 2: CANNOT_GROUP_WITHOUT_AGG

enter image description here

Upvotes: 0

Views: 3359

Answers (2)

TheMaster
TheMaster

Reputation: 50799

Issue:

CANNOT_GROUP_WITHOUT_AGG

means The query cannot use group columns without a AGGregate function.

select A,C where A is not null group by A pivot B

You're asking it to group by A. If the query groups by Column A, What to do with Column C? Column C cannot be displayed side by side. Although your sample doesn't have duplicates in B, think what will happen, if Column B is a,b,a instead of a,b,c. Column C needs to be aggregated. Said otherwise, For each group in A, What do you want to do with C(if there are multiple items in C for the same group in A)?

Solution:

Provide a Aggregate function for column C like, sum, count,max etc. For each group in A, The corresponding column C will be summed or counted or a max value of C in that group will be taken.

Snippet:

=QUERY(B21:D24, "select A, sum(C) where A is not null group by A pivot B",1)

Upvotes: 1

marikamitsos
marikamitsos

Reputation: 10573

Please use the following formula to get the 0's as well.
It is the IF function that will give you the 0's in your pivot.

=ArrayFormula(IF(QUERY(M1:O22, "select M, sum(O) where M is not null group by M pivot N")="",0, 
                 QUERY(M1:O22, "select M, sum(O) where M is not null group by M pivot N")))

enter image description here

As for the syntax, it is mentioned that

If you use a pivot clause, then every column listed in the select clause must either be listed in the group by clause, or be wrapped by an aggregation function

Functions used:

Upvotes: 1

Related Questions