2

I am trying to do a group by operation on the following table using the query language of Google sheet.

Type    Value   Duration
A         50    00:50:00
B          5    00:06:00
A         50    00:45:00
C         15    00:30:00
B          5    00:03:00
C         15    00:25:00

I am grouping by the column Type and I want the sum of Value and the average of Duration. I can do the sum of Value by:

=QUERY(A2:C7; "SELECT A, SUM(B) GROUP BY A")

But when I try to average time using, for example:

=QUERY(A2:C7; "SELECT A, SUM(B), AVG(C) GROUP BY A")

It throws the following error:

Error

It was not possible to analyse the string for the Function QUERY parameter 2:

AVG_SUM_ONLY_NUMERIC

1

Indeed, out of the six data types supported by query (string, number, boolean, date, datetime, and timeofday), the avg function can be used only on numbers. So you have to convert duration to number (the function N does this) and average that:

=query({A2:C7, arrayformula(N(C2:C7))}, "select Col1, sum(Col2), avg(Col4) group by Col1")

Here the array constructed by {A2:C7, arrayformula(N(C2:C7))} has four columns, the fourth one being the datetime converted to numbers. This is the column which gets averaged. You will need to format the column into which the average is returned as duration.

| improve this answer | |
1

The answer provided by @zaq solves the issue. But if one is using Google Sheets in Portuguese, like me, there is a small modification.

=query({A2:C7\ arrayformula(N(C2:C7))}; "select Col1, sum(Col2), avg(Col4) group by Col1")

The change ocurred inside the array with curly braces {..}, where the comma is changed by a backslash. Check the docs for a better explanation.

| improve this answer | |
  • It helps me with Russian version too. –  GhostKU Nov 22 '18 at 0:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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