Google-sheets – Group by in query Google Sheet select different columns

google sheetsgoogle-sheets-query

Want to get the results described on the screenshot. How can I achieve it.

enter image description here

QUERY: 

=QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")

Want to get First & last V5 value in terms of max and min dates in V2 (see K2, L2 desired results), I could get the last and first dates now I want their V5 corresponding.

Best Answer

I'm not sure if this will work for the entire end goal, but, based on the observation that every column is the result of a query returning one row, is it possible that you may be able to have one simple query per column, each returning one row. For example: enter image description here

The formula for columns:

  • E1: =query(Sheet1!$A$2:$A$8, "select max(A) group by A")
  • F1: =query(Sheet1!$A$2:$B$8, "select max(B) group by A")
  • G1: =query(Sheet1!$A$2:$C$8, "select min(B) group by A")
  • H1: =query(Sheet1!$A$2:$C$8, "select max(C) group by A")
  • I1: =query(Sheet1!$A$2:$C$8, "select min(C) group by A")

Notes:

  • google sheets wants to stick in the "max" and "min" title & it's a pain to remove it
  • you can remove the Sheet1, that's just there in case the data is on a different sheet (tab). Of course change it or remove it (and the trailing !) if the sheet isn't called Sheet1.
  • if it's easier to include the entire range of the whole table, it'll still work, but might impact performance(?), e.g., you can use $A$2:$C$8 in each query.
Related Topic