Return All Values in Query Group By Clause in Google Sheets

If you’re a Google Sheets user, you may already be familiar with the group by clause in Query. It’s a powerful tool that allows you to summarize and aggregate data in your spreadsheets. But what if you want to include all values, even those that don’t match your conditions? In this tutorial, I’ll show you how to return all values in the Query group by clause in Google Sheets.

How to Return All Values in Query Group By Clause in Google Sheets

To achieve the desired output, we’ll need to use a combination of three formulas: two Query formulas and SORTN. Don’t worry if you’re not familiar with SORTN, I’ll explain everything step by step.

Step 1:
Here is the formula that will give you the desired output:

=query({B2:C8},"Select Col1, Sum(Col2) Where Col2 > 100 Group By Col1 label Sum(Col2)''",0)

This formula will return the values that match your conditions and sum them up. But it won’t include any values that don’t match the conditions. To include those as well, we’ll need another formula.

Step 2:
Use this formula to summarize the values that don’t match the conditions:

=query({B2:C8},"Select Col1, 0/Sum(Col2) Where Col2 <= 100 Group By Col1 label 0/Sum(Col2)''",0)

This formula will give you the mismatched items with an aggregate value of 0.

Step 3:
Now, combine both formulas using curly braces to get the expected result:

={query({B2:C8},"Select Col1, Sum(Col2) Where Col2 > 100 Group By Col1 label Sum(Col2)''",0);query({B2:C8},"Select Col1, 0/Sum(Col2) Where Col2 <= 100 Group By Col1 label 0/Sum(Col2)''",0)}

This combined formula will return all values in the Query group by clause, including both the matched and mismatched items.

But what if you have duplicates in your data? Here’s where SORTN comes in handy.

SORTN + Query to Remove Duplicates

If you have duplicates in your data, using the above Query combo will include them as well. To eliminate duplicates, you can use the SORTN function.

Here’s an example:

Let’s say you have the following data:

Apple 150
Mango 150
Orange 150
Apple 150
Mango 50
Orange 150

Using the above Query combo will give you the following output:

Apple 300
Mango 150
Orange 300
Mango 0

As you can see, the value “Mango” is duplicated. To remove duplicates, you can use the SORTN function.

The final formula would look like this:

=SORTN({query({B2:C7},"Select Col1, Sum(Col2) Where Col2 > 100 Group By Col1 label Sum(Col2)''",0);query({B2:C7},"Select Col1, 0/Sum(Col2) Where Col2 <= 100 Group By Col1 label 0/Sum(Col2)''",0)},9^9,2,1,1)

The SORTN function will ensure that only unique values are returned, giving you a clean and concise output.

And there you have it! By using a combination of Query and SORTN formulas, you can return all values in the Query group by clause in Google Sheets.

If you’re interested in learning more about Array Formulas, be sure to check out our website Crawlan.com for more tutorials and tips.

Happy spreadsheet-ing!