I admit the title Return All Values in Query Group By Clause is not giving enough idea about this tutorial. I am trying to explain that in the below two-three paras and the following screenshot. New to Query? Then first check this guide – Learn Query Function with Examples in Sheets.

The group by clause in Query helps us (Google Sheets users) to summarise data in Google Sheets. In other words, the group by clause in Query helps us to aggregate values across rows.

The advantage of the group by clause is it can accept conditions. Also, as said, there must be the use of any aggregation functions as part of the group by clause.

When you use conditions in Query grouping and use any aggregation functions, you will get the summary of the items that match the conditions. But I want to include the mismatched items in the summary with the aggregate value as 0. That’s what I meant by the above title Return All Values in Query Group By Clause in Google Sheets.

Task/Example: Group and Sum the items in Column B if the corresponding values in Column C are greater than 100.

Expected Output: In the output, I want the formula to group and sum as per the above task. But additionally, I want the formula to return the mismatched items in the result with the aggregate value as 0.

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

You can achieve the above-expected output with a combination of three formulas – Two Query formulas + SORTN.

The function SORTN in Google Doc Sheets may be new to many of you. But the prior knowledge of using SORTN is not a must to understand this tutorial.

Here is the step by step instructions to return all values in Query group by clause in Google Sheets. In the example, I am using the Sum aggregation function. You can use Count, Max, Min etc. as per your requirement.

Related Reading: How to Sum, Avg, Count, Max, and Min in Google Sheets Query.

Step 1:

Here is the formula that returns the output in the range F2: G5 in the above screenshot.

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

This formula only returns part of the values in Column B since other values do not match the conditions (the value must be >100 in Column C).

Step 2:

With another formula, we can summarise the values that do not match the above condition.

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

Output:

Banana 0
Grape 0
Peer 0

In this Query, I have summed the items if the values are <=100. But in order to get 0 instead of the sum, I have opted to divide each sum values with 0.

Step 3:

Combine both the above Query formulas using the Curly Braces. You will get the expected result.

You May Like: Array Formula: How It Differs in Google Sheets and Excel.

I mean the formula thus combined can return all values in Query group by clause in Google Sheets.

={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)}

Some of you may set with this formula. But when using this formula, a few of you may find issues with your real data. You may want more refinement.

There comes the use of SORTN. So don’t use the above as the final formula. Just read on to get that.

SORTN + Query to Remove Duplicates

Note: When you have time, I suggest you, learn SORTN and its Tie Modes. You will love this function for its capability to eliminate duplicates in Google Sheets.

Here is a new sample data.

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

In this sample data, you would get the following output when you using the above Query Combo.

Apple 300
Mango 150
Orange 300
Mango 0

It does include all the values. But the value “Mango” is duplicate, right? Here comes the role of the function SORTN.

SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])

Use the above Query combo as the range in SORTN.

The final formula would be as follows.

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

Other than the range, the last part of the formula 9^9,2,1,1)i.e. [n], [display_ties_mode], [sort_column], [is_ascending]) would remain the same for any two column data with duplicates in the first column.

In the above example, I have used infinite ranges too (highlighted in Green).

That’s all. Use the above formula to return all values in Query group by clause in Google Sheets. Enjoy!