1

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Frischling
  • 2,100
  • 14
  • 34

2 Answers2

1

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)''"; )
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    I went with the labeling solution, to avoid double querying (I want to do a lot of queries in a column... labeling is hopefully performing better than querying) – Frischling Dec 08 '22 at 08:26
0
=QUERY(Departments!B:N; "select N, max(C) as MaxC, max(M) as MaxM, max(J) as MaxJ where N="&A4&" group by N"; 0)

or

=PIVOT(Departments!B:N; N; C, M, J; "MaxC", "MaxM", "MaxJ")
pythonic
  • 33
  • 4
  • in google sheets I tried the ; 0 at the end, for aggregates this did _not_ remove the function name. Pivot does not seem to be a google sheets thing? – Frischling Dec 08 '22 at 08:27