Google-sheets – Google sheet query to combine values

google sheetsgoogle-sheets-query

From various examples I've hacked together a pretty neat aggregation function which gives me an overview on which activity (strings in G) I've spent non-billable work hours (numbers in E) or billable (numbers in E) hours of work.

=query(E35:G232, "select G, sum(E), sum(F) where G != '' group by G order by sum(F) desc label G 'activity', sum(E) 'work hours', sum(F) 'billable hours'")

So this might give me:

Development |  5 | 12
Marketing   | 14 |  5
Admin       |  0 |  2

What I've tried, but so far failed, to do now is getting this same list with the combined hours per activity, and also sort it by that combined number. I tried combining the select with combining the sum of columns, but that gives me an error:

=query(E35:G232, "select G, sum(E), sum(F), sum(E:F) where ...

How can I further combine the grouped values to get the overall values per activity to get something like this, additionally sorted by highest combined?

Marketing   | 14 |  5 | 19
Development |  5 | 12 | 17
Admin       |  0 |  2 |  2

Best Answer

You can use a double query like:

=QUERY(query(E35:G232, "select G, sum(E), sum(F) where G != '' group by G order by sum(F) desc label G 'activity', sum(E) 'work hours', sum(F) 'billable hours'"),"Select Col1,Col2,Col3,Col2+Col3 order by Col2+Col3",1 )

Explanation

arrayformula(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false))

creates a column with the order you want. This column gets appended to the queried array using array notation:

{A2:AB, arrayformula(...)}

This new array can now be queried. However, since it's a new array and not something placed in the sheet, one cannot use letters A,B,C... to refer to columns. Instead, one has to write Col1, Col2, etc:

"select Col3, Col1, Col2 where Col2 = 'New York' order by Col4"

Error handling

What if some values in the table are not listed in your order, e.g., null values? Add iferror clause to sort them to the bottom:

arrayformula(iferror(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false), 1000)

This gives the sorting order 1000 to the values not listed.

Helper column alternative

If having to switch to Col1 notation is too much of a price to pay, add the helper column

=arrayformula(hlookup(E2:E, {"str1","str2","str3",""; 1,2,3,4}, 2, false))

to the sheet; it would be column AC for you. It can be made hidden so it doesn't get in the way. Then query normally and sort by AC.

Lazy alternative

If there are not too many options, you can just repeat the command and join the results using array notation:

= {query(..., "... E='string1'"); query(..., "... E='string2'"); ...}

Like this:

={ QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York' AND E = 'string1'"); QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York' AND E = 'string2'") }

You'll need to avoid header rows when doing this, because they would be repeated.

Google-sheets – Query a Google Spreadsheets using several different values

As query language documentation says,

You can join multiple conditions using the logical operators and, or, and not. Parentheses can be used to define explicit precedence.

Example:

select * where J = 'Guest Speaker' or J = 'Field Trip' or J = 'Anything'

Best Answer

Related Solutions

Google-sheets – Defining a custom order in a Google Sheet query

Explanation

Error handling

Helper column alternative

Lazy alternative

Google-sheets – Query a Google Spreadsheets using several different values

Related Topic