arraysgoogle-sheetslambdagoogle-sheets-formulagoogle-query-language

How do I select multiple different counts with different where clauses in Google Sheets query language?


I'm currently trying to select multiple counts with different criteria. Unfortunately, I am getting a PARSE error when I try to combine them into the same query. They work fine individually, just not together. Anyone know how to fix this?

This works fine (separately)

Select count(Col2) where Col2 <= 180 

or 

Select count(Col2) where Col2 > 180

or

Select count(Col2)

This doesn't work (combined)

Select count(Col2) where Col2 <= 180, count(Col2) where Col2 > 180, count(Col2)

Error that I get when I try

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered

Solution

  • you must use only one where

    "select count(Col2) where Col2 <= 180 or Col2 > 180"
    

    OR logic can be exchanged with AND logic if/when needed


    try:

    =INDEX(LET(a, A:B, {
     QUERY(a, "select count(Col2) where Col2 <= 180"), 
     QUERY(a, "select count(Col2) where Col2 > 180"), 
     QUERY(a, "select count(Col2)")}))