Retain All Values When Using QUERY GROUP BY in Google Sheets

Published on

We often use the QUERY function to aggregate data in Google Sheets, and the GROUP BY clause helps with this. However, when using this clause, only aggregated values are returned, omitting individual row details. Fortunately, there is a way to retain all values in QUERY GROUP BY—by adding a helper column to your source data.

This tutorial will show you how to structure your data and formula to retain all values within each group, ensuring you don’t lose important row-level details.

Sample Data and Regular Aggregation with Missing Row-Level Data

Assume you have the following sales data in A1:C:

CategoryItemSales (kg)
FruitsRambutan12.5
FruitsBlueberry8.2
FruitsOrange20
VegetablesCarrot15.4
VegetablesTomato18.7
VegetablesSpinach9.3
VegetablesSpinach7.8

To get a category-wise summary, you can use the following QUERY formula:

=QUERY(A1:C, "select A, sum(C) where A is not null group by A", 1)

Result:

Categorysum Sales (kg)
Fruits40.7
Vegetables51.2

The formula selects and groups column A (Category) and totals column C (Sales). Since we used an open range (A1:C), we opted to filter out empty rows in the data to ensure a clean result.

Here, the row-level data is missing because the ‘Item’ column is omitted. We want to include the Item column as well, but without grouping it.

However, every column used in the SELECT clause must also be included in the GROUP BY clause. This means the following formula will not work:

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

Example of Retaining All Values When Using QUERY GROUP BY

As mentioned earlier, we must add a helper column to the source data. In cell D1, enter the following formula:

=MAP(A1:A, LAMBDA(val, TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B1:B, A1:A=val)))))

Where:

  • A1:A is the group column (Category).
  • B1:B is the Item column, which contains the values we want to retain when using QUERY GROUP BY.

Now, use the following QUERY formula:

=QUERY(A1:D, "select A, D, sum(C) where A is not null group by A, D", 1)

Compared to the previous formula, here we:

  • Expanded the data range to A1:D to include the helper column.
  • Selected and grouped column D.

Result:

CategoryItemsum Sales (kg)
FruitsRambutan, Blueberry, Orange40.7
VegetablesCarrot, Tomato, Spinach51.2

This approach helps you retain all values when using QUERY GROUP BY in Google Sheets.

Helper Column Explained

=MAP(A1:A, LAMBDA(val, TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B1:B, A1:A=val)))))
  • FILTER(B1:B, A1:A=val): Filters the items where the category matches the current row category.
  • UNIQUE(...): Removes duplicate items.
  • TEXTJOIN(", ", TRUE, ...): Joins the items into a comma-separated list, excluding empty values.
  • The LAMBDA function applies this logic to all categories using MAP.

This helper column is the key to retaining all values or row-level data in QUERY GROUP BY in Google Sheets.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

5 COMMENTS

  1. Hi Prashanth,

    I have created a Google Sheet, and it contains a database.

    I need them into the group by Brand, by ASIN, by MKSU, and the sum of column E to K. Each Brand, each ASIN, Each MSKu need the sum of the totals.

    I have got all databases using Query, and I could not be able to get the group-wise.

    Kindly help with this. I herewith attached the screenshot for the same along with the Google Sheet with command.

    • Hi, Saravanan,

      I have inserted one QUERY formula in cell A1 in the tab “prashanth kv”.

      See if that helps?

      If not, please refer to the “Pivot Table 3” tab.

      That formula is actually pretty simple to code. If you want that formula explanation, please let me know.

  2. Hey! Thanks for this nice post.

    I was following the tutorial and trying to wrap my head around how SORTN works.
    But then I noticed there is an even simpler (well at least easier to understand for my feeble brain) way of achieving the same outcome.

    In step number 3, instead of using SORTN to eliminate duplicates, you could just query the resulting array again and do this: Select Col1, Sum(Col2) Group By Col1.

    The outcome is the same, because x + 0 = x.

    Thanks anyway for this great post, it leads me to solve my problem.

    best,
    – J

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.