143

I'm doing this query at my google spreadsheet:

=QUERY(H4:L35;"select sum(L) where H='First Week'"; -1)

But it returns a little table with "sum" as header and result below it. What I want is just the result! How I remove header? Can I?

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Poliane Brito
  • 2,142
  • 3
  • 18
  • 26

9 Answers9

268

Try this:

=QUERY(H4:L35,"select sum(L) where H='First Week' label sum(L) ''")
General Grievance
  • 4,555
  • 31
  • 31
  • 45
KRR
  • 4,647
  • 2
  • 14
  • 14
  • 3
    I just tested this in one of my sheets and it does remove the header from the returned results; this should be marked as the correct answer. :) – FooBar Feb 16 '15 at 17:15
  • Just a note, the `label` command looks like must to be at the end, even after any `order` command. – fguillen Oct 08 '15 at 13:15
  • 1
    This is great and it works, but could anyone explain why the two single quote marks are needed at the end? What does that do from a syntax perspective? – y-i_guy Mar 29 '16 at 17:21
  • 2
    The single quote replaces the sum header with empty space. Hope that explains your question!! – KRR Mar 29 '16 at 18:24
  • 2
    Thanks for the answer! I need to do the same with more than one column but I cannot achieve it! I expected this will work: =QUERY(H4:L35, "select sum(L), sum(H) where H='First Week' label sum(L) '' label sum(H) ''") – Emilio Nicolás Oct 14 '16 at 16:28
  • 17
    ``LABEL B 'Label1', C 'Label2'`` for multiple columns – sojim2 Nov 05 '17 at 17:33
  • 1
    NOTE: It's required that you does this "blank labeling" for every field being selected, or it won't work. – Camden S. Jul 14 '18 at 16:32
  • Another way of doing this **THAT YOU MIGHT THINK WOULD WORK** is to add 0 as the last parameter (the number of headers) to QUERY function. This would be an easier solution in the case of multiple columns. Comment below next answer from @dave-meindl explains SUM function adds headers. `QUERY(data, query, [headers])` ```headers - [ OPTIONAL ] - The number of header rows at the top of data. If omitted or set to -1, the value is guessed based on the content of data. ``` Explained here (same text as pop up help): https://support.google.com/docs/answer/3093343?hl=en – gaoithe Feb 21 '20 at 11:50
  • This `query` function is so full of black magic... I wonder what sane user would intuitively think about this solution (which surprisingly work, thanks KRR!). – Eran Dec 14 '20 at 07:42
50

=QUERY(QUERY(A1:D, "SELECT *", 1), "SELECT * OFFSET 1", 0)

The outer query: "SELECT * OFFSET 1" excludes the first row (the header).

The inner query explicitly specifies one row of headers (via the third argument supplied to QUERY), whilst the outer query specifies none.

ptim
  • 14,902
  • 10
  • 83
  • 103
user280150
  • 601
  • 5
  • 2
  • 3
    A bit of explanation? – J Fabian Meier Jul 08 '16 at 09:46
  • 1
    Why is the second `QUERY` necessary? Doesn't the "0" parameter drop the headers, so you can use it on the first `QUERY`, like this: `QUERY('Form responses 3'!$A$2:$P$1017, "SELECT max(E),max(C), max(D) WHERE B='" & B14 & "' GROUP BY B LIMIT 1 ", 0)` – Steven M. Mortimer Jan 03 '17 at 20:57
  • 3
    I believe `OFFSET 1` in the second query will drop the very first row of the returned dataset, not the headers in the first row. – Steven M. Mortimer Jan 03 '17 at 21:03
  • 3
    This answer may be more verbose but it is also more robust and can be used without needing to duplicate column aggregation strings. It can even be used with `pivot`, which does not work with any other answer here. – 7yl4r Oct 05 '17 at 18:14
  • The row that will be dropped via `OFFSET` is determined by the combination of values provided to the third parameter of the `QUERY` - I recommend setting them explicitly rather than relying on `-1` which asks the application to interpret the data. – ptim Mar 07 '19 at 01:46
  • Totally amazing answer – Tallboy May 04 '19 at 20:17
  • 1
    Unless I'm mistaken, this does not actually solve the specific challenge offered by the original poster, i.e. removing the headers that are automatically generated by QUERY when using aggregation functions such as SUM. When using SUM (or any other aggregation function), QUERY automatically creates a header row specifying the aggregation performed, e.g. "sum". It will do this even if you set headers to 0 and even if you apply the OFFSET 1 clause. The following formula still returns headers. =QUERY(Sheet1!A:B,"SELECT A, SUM(B) GROUP BY A OFFSET 1",0) The accepted answer solves this challenge. – Dave Meindl Jun 19 '19 at 15:26
  • If all you have is a hammer, everything looks like a nail. Correct answer, but the outer query is a little too much for that. Using INDEX for that would make more sense. Or, obviously, the currently accepted answer, which is simpler – espinchi Aug 01 '20 at 17:44
  • I just did a `=query(C6:Q300, "select sum(E), sum(F), sum(G), sum(H), sum(I), sum(J), sum(K), sum(L), sum(M), sum(N), sum(O), sum(P), sum(Q) where C matches 'targetdata' label sum(E) '', sum(F) '', sum(G) '', sum(H) '', sum(I) '', sum(J) '', sum(K) '', sum(L) '', sum(M) '', sum(N) '', sum(O) '', sum(P) '', sum(Q) '' ", 0)`. Hoped I'd seen your answer sooner ;) – taiyodayo Mar 01 '22 at 03:29
6
=INDEX(QUERY(H4:L35;"select sum(L) where H='First Week'"; -1),2,1)

This just parses the returned array and selects the 2nd record returned in the first column.

You can also do this with the filter function which is less compute intensive. 

=SUM(FILTER(L4:L35, H4:H35 = "First Week"))
robsiemb
  • 6,157
  • 7
  • 32
  • 46
Dean Falconer
  • 61
  • 1
  • 2
  • Thanks - with a complex expression being used to build the result, using the `INDEX()` approach is much cleaner than duplicating the expression in a `LABEL` clause. – Alex Jun 14 '23 at 17:49
1
=QUERY(H4:L35;"select sum(L) where H='First Week'"; -1)

Just make a SUM of the QUERY result - like this:

=SUM(QUERY(H4:L35;"select L where H='First Week'"))

With this solution, you can also calculate with the cells and then add everything up.

=SUM(QUERY(H4:L35;"select L*(S-R) where H='First Week'"))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
andyJK
  • 49
  • 2
0

I have a QUERY which is returning the top 3. I could not get this to work when returning multiple rows. I ended up just hiding the row with the formula and only the answers show now.

Brian Wochele
  • 325
  • 1
  • 6
  • 13
0

For queries using pivot, try using INDEX to remove headers from the pivoted columns.

=INDEX(QUERY('Class hours'!A2:C11,
             "select sum(C) 
              where A = '"&A5&"' 
              group by A 
              pivot B"), 2)

Got the answer from this thread: https://stackoverflow.com/a/63468791/5424088

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Oscar Manrique
  • 31
  • 3
0

... or this

=QUERY(QUERY(H4:L35;"select sum(L) where H='First Week'"),"OFFSET 1",0)

This is more concise when ALL label classes are not wanted.

Note that 'select' and 'where' classes are not required in the second QUERY statement.

DeeKay789
  • 353
  • 2
  • 4
  • 8
-1

Instead of labeling column names as blanks using '', you can omit all headers like this:

=QUERY(H4:L35,"select sum(L) where H='First Week'", 0)

Steven M. Mortimer
  • 1,618
  • 14
  • 36
  • Whist the accepted answer appears be more technically correct, this looks the simplest method to resolve the issue and easier to remember. Should be more love for this answer unless I have missed something – Ewen Mar 03 '17 at 04:40
  • 22
    This answer does not remove the "sum" as a "header row". It does tell the query command that there are no header rows to consider in the data being queried, but does not stop the query from adding an "unintended header" to the results table. – Jimmy Mar 30 '17 at 18:28
-3

See the format here.

Example:

=QUERY(B4:C38,
   "SELECT C, sum(B) where C!='' group by C label C 'Member', sum(B) 'Sum'"
)
hichris123
  • 10,145
  • 15
  • 56
  • 70
Andy
  • 23
  • 5