0

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
  • You can't. Use a double query: =query(query()) – TheMaster Jul 9 '18 at 14:09
  • Or just add a formula next to the query results – Rubén Jul 9 '18 at 17:17
  • @I'-'I Can you give me an example? I suppose the subquery would be generating a col of combined values in this case? – kontur Jul 11 '18 at 8:40
  • @Rubén The query affects the sorting, and I would like to sort all 4 columns in rows with combined hours highest. Can a formula do that, how? – kontur Jul 11 '18 at 8:41
1

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 )
  • Impressive. So you can use one query to generate a new data source "on the fly", then use a query on that data to do the summation and sorting. One question though, are those Col1, Col2, etc. special syntax referring to a query result? I can't find any documentation where to read up more on this. – kontur Jul 11 '18 at 9:50
  • 1
    @kontur If the input to query is a range in a sheet,We use column identifiers(A,B,E,..). If not,We use numbers,Col1,Col2 .. – TheMaster Jul 11 '18 at 10:10

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.