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.
