# Group by in query Google Sheet select different columns

Want to get the results described on the screenshot. How can I achieve it. 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.

• Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Jun 1 at 2:15
• @marikamitsos Find the shared sheet: docs.google.com/spreadsheets/d/… – Mostav Jun 2 at 19:19
• @marikamitsos, any feedback or hint? thank you – Mostav Jun 3 at 13:40
• Welcome to Web Applications. Please add a brief description of your search/research regarding the result you are looking for as is suggested in How to Ask. – Rubén Jun 4 at 23:12
• I worthy to note that the result you are looking could not be achieved by using a single QUERY function, in other words, you should use another formula or use a single but complex formula. If the values in B are unique you could use them as "keys" and use VLOOKUP, INDEX and MATCH or other similar lookup soluctions. – Rubén Jun 4 at 23:15

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: 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.

You can even have your results in a single long formula in cell `H1`

``````={QUERY({A2:F},"SELECT Col1, MAX(Col2), MIN(Col2) where Col1 is not null group by Col1 label Col1 'G V1', MAX(Col2) 'LAST V2', MIN(Col2) 'FIRST V2' "),
QUERY({"LAST V5", "FIRST V5";IFERROR(ArrayFormula(VLOOKUP(QUERY({A2:F},"SELECT MAX(Col2), MIN(Col2) where Col1 is not null group by Col1 label MAX(Col2) 'LAST V2', MIN(Col2) 'FIRST V2' "),B2:F,{4,4},0)))})}
``````

(I would personally go against it since you mention: "Actually, i am not expert in google sheets...")

Please use the following formula in cell `K2`

``=IFERROR(ArrayFormula(VLOOKUP(I2:J,B2:E,{4,4},0)))`` In the formula we used open ranges. That means that the ranges are in the form `I2:I`, `B2:E` etc.

The same way you can extend your original query formula to the open range `A2:F`

``````=QUERY(A2:F,"SELECT A, MAX(B), MIN(B) where A is not null group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")
``````

Notice that we added the `where` clause `where A is not null` to omit empty cells.

Functions used: