# Average time on group by using Google sheet query language

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

## 2 Answers

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.

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.

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