0

Looking to work around with error message "Add_col_to_group_by_or_agg:Col1" from formula below:

Query(Query({data range},"Select etc."), "Select (Col1*Col3)/Sum(Col1)",0)

The inner query Query({data range},"Select etc.") is working, the output contains 3 columns, with same number of rows, arranged like below:

Col1 | Col2 | Col3
1.00 | 11 | 25
2.00 | 21 | 21
3.00 | 10 | 22,etc.

And I'm looking to wrap this result in another query to using the criteria "Select (Col1*Col3)/Sum(Col1)", which unfortunately I cannot get it working.

I've tried it is working totally good if just using partial criteria "Select Col1*Col3" or "Select Sum(Col1)", but when combined the two with the divide, I couldn't get it working, error message as mentioned earlier, "Add_col_to_group_by_or_agg:Col1".

I cannot use =arrayformula here, because I've to use the Col numbers but not the Col name.

Improve this question
2
  • 2
    I read your previous post when you posted it. But I had to skip over it, because it did not contain realistic data, ranges or expected results. Here, you've included some sort of data, but no expected results. The most efficient way for those here to help you will be if you share a link to your spreadsheet (or a copy of it) wherein you've got at least enough data to illustrate the problem as well as the manually-entered results you would like to see, where you'd like to see them. Be sure to set the link permissions (when creating the link) to "Anyone with the link..." and "Editor."
    – Erik Tyler
    Oct 7, 2021 at 2:15
  • Thanks for that, I think I found the solution through another post, I've posted the answer below just now
    – decompluse
    Oct 8, 2021 at 4:12

1 Answer 1

Reset to default
0

I've found the answer to my own question, through another post https://stackoverflow.com/a/54608779

It seems query only allow one of two things in a single query, so I cannot do both divide by & sum in one go. Alternatively I used another method from the other post to reach the solution.

Arrayformula(Query(Query({data range},"Select etc."), "Select (Col1*Col3)",0)/Query(Query({data range},"Select etc."), "Select Sum(Col1)",0)

What I did was, separated the query into 2 parts,
1)do query (Col1*Col3);
2)do query Sum(Col1)

Then warp them using arrayformula Arrayformula(query1/query2).

Improve this answer

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.