CODEWITHSUNDEEP
google-sheetsgoogle-sheets-querygoogle-query-language
Stephen Lester
Stephen Lester

Reputation: 358

Google Query Language for sum of result using cell reference in query

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

Upvotes: 1

Views: 1937

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34220

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.

Upvotes: 1

Related Questions