How do you change the default aggregate name created in Google Sheets when using the QUERY() function?
To change the header label of an aggregate column from a QUERY() function append LABEL aggregate_column 'YOUR LABEL' to your SELECT statement.
For example, if you had the following QUERY formula in your Google Sheet and you wanted to change the default label of sum(Sales Qty) to Total Sold then this is how this would look, here’s the original QUERY() formula:
=QUERY(Data!A:C, "SELECT SUM(B), SUM(C)")Here’s how this formula looks by default as seen in the Agg1 sheet:
Custom Aggregate Column Name
To create your own label on the aggregate column, use the LABEL syntax in your query statement. If you wanted to add a more precise name to the SUM(B) column header, all you would need do is append the following syntax to your SELECT statement:
=QUERY(Data!A:C, "SELECT SUM(B), SUM(C) LABEL SUM(B) 'Total Sold', SUM(C) 'Total Sales'")The main thing to emphasise when appending a label, just as it is here with LABEL SUM(B) 'Total Sold' that I’d like to emphasise, is that the aggregate column defined after the word LABEL needs to be written exactly the same as it is in the SELECT area.
As you can see with the example, the SELECT has one aggregate column defined as SUM(B) therefore, this needs to be used in the LABEL area if you wanted to rename this column header.
This is true even if the aggregate function is a formula containing multiple aggregates, for example, if you had an aggregate column like SUM(B) - SUM(C) to create a custom label for that aggregate, you would need to append the same in the label area, like LABEL SUM(B) - SUM(C) 'Differences'.
Here’s how the resulting aggregate result now looks on the spreadsheet in sheet Agg2 when you name your aggregate columns using the LABEL feature:
Aggregate Names Not Changing
If you are experiencing problems with using the LABEL in your query statement when using the QUERY function check each of the following:
1. The LABEL is using the correct aggregate function(s) in the SELECT area of your statement. Remember, the syntax needs to be LABEL aggregate_column 'Custom Name' where aggregate_column needs to match the aggregate column in the SELECT area.
2. The name of the aggregate label is wrapped in single quotes.
3. The position of LABEL is towards the end of your statement.
I have found one of these to be the reason why a name for an aggregate column did not work.
Summary
Aggregating your data in a spreadsheet can make finding data a lot easier, however, the output of an aggregate result may look a little ugly.
To change the default name produced by Google Sheets from an aggregate column use the LABEL syntax in your query statement and ensure the LABEL aggregate_column matches the function used in the SELECT section of your query statement.
