Beginner: I am trying to average a column of time on SQL based off of a value in another column

General Q&A
,
1

I am a beginner on SQL and I am trying to learn more about it and how to manipulate data on here. I am trying to get an average time and SQL is saying the “avg” function cannot be used for that.

Below is the code:

SELECT

AVG(ride_length) AS avg,

MIN(ride_length),

MAX(ride_length)

FROM

scenic-arc-373908.Cyclistic_Data.04_2020

WHERE

member_casual = ‘casual’

I then get the following error:
No matching signature for aggregate function AVG for argument types: TIME. Supported signatures: AVG(INT64); AVG(FLOAT64); AVG(NUMERIC); AVG(BIGNUMERIC); AVG(INTERVAL) at [2:1]

2

Hi. Are you using Bigquery?

You need to transform ride_length to some of the numeric options (FLOAT64 perhaps). You can use CAST and FORMAT_DATE() for that.

Also, try not to use aliases for your calculations that are function names.

1 Like