Average time on group by using Google sheet query language

Question

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

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

Answer 2

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.