1

Hello and thanks for your help. I'm new to GQL but have good SQL experence and think I may be missing something small. I have 2 sheets i'm working with

Main sheet

Colum G
InstanceID
i-554532f4693fc6186
i-09554fcda5f2f3262
i-0047551ae514412d5

-

Data Sheet

Colum A                 Colum B
i-554532f4693fc6186      10.12
i-554532f4693fc6186      12.12
i-554532f4693fc6186      13.12
i-554532f4693fc6186      17.12
i-554532f4693fc6186      30.12

I am trying to write a query that will find all the rows that match the Instance ID in column G against the datasheet Column A and return the AVG of all the matches in column B, the top 5 max, and top 5 min.

I'm finding that I can't point the query to a cell for referencing the instance ID. Is there a way? I'm using this to try to get the max and it works for 1 but I ned the top 5 or any number.

=sort(query('HeC-Metrics'!A:B,"select max(B) Where A = 'i-044532f4693fc6186'"))

I'm OK needing to do different queries for each of the required results, AVG, min, max. I would also like to reference the cell in the G column so I don't have to manually enter the InstanceID.

Thanks your time. Stephen

Stephen Lester
  • 348
  • 3
  • 16
  • Any errors popping up? Not sure why you're trying to wrap a `QUERY` with `SORT` when you can use `order by` in your query statement. – tehhowch Aug 25 '18 at 00:07
  • 1
    Also, is this question useful? https://stackoverflow.com/questions/23427421/query-syntax-using-cell-reference ? – tehhowch Aug 25 '18 at 00:09

1 Answers1

1

So it's just a case of getting the right syntax to use a cell value as a match in the query

=query(Sheet2!A:B,"select avg(B) where A='"&G2&"' group by A label avg(B)  ''",1)

Note that you don't really need the group by if you already have a list of distinct ID's to compare against, but you can't have an aggregate like avg without it.

To get the bottom 5, you can use filter & sortn

=transpose(sortn(filter(Sheet2!B:B,Sheet2!A:A=G2),5))

(I have transposed the result to get it in a row (row 2) instead of a column)

or you could use a query

=transpose(query(Sheet2!A:B,"select B where A='"&G2&"' order by B  limit 5 label B '' ",1))

Similarly to get the top 5 you could use

=transpose(sortn(filter(Sheet2!B:B,Sheet2!A:A=G2),5,,1,false))

or

=transpose(query(Sheet2!A:B,"select B where A='"&G2&"' order by B desc limit 5 label B '' ",1))

This begs the question of whether you could get these results (a) without needing a list of distinct values and (b) in a single array formula without copying down.

You could certainly get the distinct ID's and averages straight away from a query. Getting the top or bottom n values from a number of groups is much more difficult. I have attempted it in a previous question, but it requires a long and unwieldy formula.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • This worked out great. Any chance you know how to get a query to report back 2 standard deviations from the mean? I'm trying this. =query('Sheet2'!A:B,"select (mean(B)+(2*stdev(B))) where A='"&G2&"' group by A label (max(B)+(2*stdev(B))) ''",1) – Stephen Lester Aug 29 '18 at 19:39
  • 1
    Well you only have the basic aggregate functions avg count max min and sum in a query, so it looks as if you'd have to go for a filter/stdev and filter/average combo, maybe using helper cells to make it easier, and just use the results. – Tom Sharpe Aug 30 '18 at 08:11