Search code examples
google-sheetssumlabelmaxgoogle-query-language

query: Remove aggregate function name for header

I have simulated joining in google sheets with one =query function per row. If I put an aggregate function in there, this does not work anymore, since I'll always get two rows at least: 1 for displaying what aggegate function I have, one displaying the result:

=QUERY(Departments!B:N;"select N, max(C),max(M), max(J) where N="&A4&" group by N";0)

results in

            max         max     max 
   21001    27.11.2022  20000   40000

even though I have the "headers" set to 0.

this is probably a bug in google sheets - but is there another way to get rid of the max's row?

Solution

  • use:

    =QUERY(QUERY(Departments!B:N;
 "select N,max(C),max(M),max(J) 
  where N="&A4&" 
  group by N"; ); "offset 1"; )

    or:

    =QUERY(Departments!B:N;
 "select N,max(C),max(M),max(J) 
  where N="&A4&" 
  group by N 
  label max(C)'',max(M)'',max(J)''"; )