59

I have a query like this =QUERY(B2:C9; "select (C * 100 / B) - 100") in my Google Sheets. What is displayed as a column header is:

difference(quotient(product(100.0()))100.0()).

I want to put a human readable description there instead.

How can I achieve this?

Guillaume Perrot
  • 4,278
  • 3
  • 27
  • 37
  • 1
    Note that if you want to eliminate the returned header entirely then you can `label (C*100/B)-100 ''`, as explained [here](https://stackoverflow.com/a/26897291/503688). – yoyo Jun 03 '22 at 20:22

4 Answers4

65

=QUERY(B2:C9;"select (C*100/B)-100 label (C*100/B)-100 'Value'")

https://developers.google.com/chart/interactive/docs/querylanguage#Label

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • 1
    @Andrew the part following label has to match the select statement exactly. if you have `select sum(B) ... ` then your label has to be `label sum(B) 'labelname'` – mix3d Sep 26 '17 at 14:20
  • 2
    @mix3d then why is this not working ? `"select I, sum(J) label I 'aaa', sum(J) 'bbb'"` – stallingOne May 18 '18 at 11:04
  • 1
    @stallingone if you are aggregating (sum), you require a `group by I`. – AdamL Jun 05 '18 at 23:06
  • It doesn't work when the label references a dynamic column name, like `(C*100/B)-"&A1&" label ?? 'Value' '") ` . `Col1` gives error `LABEL_COL_NOT_IN_SELECT`, as it doesn't seem to be permitted when input range is not an array – arno Aug 06 '19 at 14:20
  • 1
    @arno it should work if the label matches the select exactly: `(C*100/B)-"&A1&" label (C*100/B)-"&A1&" 'Value'"` – AdamL Aug 11 '19 at 23:11
  • 3
    This answer is correct, but it still took me a while to get it correct so throwing my example here in case it helps anyone. Note that the label is the last part of my query ```=QUERY('Sprint Commitments Log'!C1:E42,"select C, sum(E) group by C label sum(E) 'Points'")``` - – noahpc Jan 13 '20 at 19:59
  • It helped me greatly! Thx, @AdamL! – Sunghee Yun Dec 27 '20 at 14:10
24

Remember there is a trick there.

Working example of the query:

"SELECT C, COUNT(C), AVG(G), AVG(E) GROUP BY C ORDER BY COUNT(C) DESC LABEL COUNT(C) 'My count' FORMAT AVG(G) '##0.00', AVG(E) '##0.00'"

Not working example of the query:

"SELECT C, COUNT(C) LABEL COUNT(C) 'My count', AVG(G), AVG(E) GROUP BY C ORDER BY COUNT(C) DESC FORMAT AVG(G) '##0.00', AVG(E) '##0.00'"

Also not working example of the query:

"SELECT C, COUNT(C), AVG(G), AVG(E) GROUP BY C ORDER BY COUNT(C) DESC FORMAT AVG(G) '##0.00', AVG(E) '##0.00' LABEL COUNT(C) 'My count'"

It works ONLY if it's placed in the correct order with other commands.

Neo
  • 289
  • 3
  • 5
  • This was what I kept getting hung up on. For me I needed to add `label` commands between `group by` and `format`. Cheers! – localshred Nov 07 '19 at 19:39
  • That's a **trick**? I didn't know following the documentation was a **trick**. It clearly says ["The order of the clauses must be as follows: ..."](https://developers.google.com/chart/interactive/docs/querylanguage#language-clauses) – Daniel Jun 12 '23 at 18:56
14

It's counter-intuitive, but you must define your relabeled column TWICE; once in the "SQL" string, and then append the label clause to the end of the SQL string.

So, if you want to select A, B, C with "B" being labeled as "Foo", you would do this:

=QUERY(B2:C9;"select A, B, C   label B 'Foo' ")

If you're doing calculations, be careful to exactly match the SQL string definition and the label definition. For example:

=QUERY(B2:C9;"select A, B*2, C   label B*2 'Foo' ")

https://developers.google.com/chart/interactive/docs/querylanguage#Label

Camden S.
  • 2,185
  • 1
  • 22
  • 27
10

If you want to rename more than one column, it uses one LABEL keyword, then separate the fields and their new name assignments using a comma.

"select B, D, D*C where D <> 0 label D 'Staff', D*C 'Cost'"
Eduardo Cuomo
  • 17,828
  • 6
  • 117
  • 94
Scott Jablow
  • 101
  • 1
  • 2